relationship table

guys, do yall link the tables together in the relationship table like this? or do yall not use the relationships at all? i understand the concepts behind referential integrity and cascading deletes etc, but it just seems that it's more prone to unexpected things - or rather out of our manual control.

what do you guys usually do?relationship
i deleted some records from my tblUnitTime which only has referential integrity and now it seems like my database is completely screwed. sigh..... or rather the queries are just now running now or take like a million years - when it's suppose to take shorter time cause there are less things to refer to now right? i have no idea. sigh... i'm screwed again.
developingprogrammerAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
In terms of your problem, I would suggest that you compact your database and see if that puts things right.  

As for relationships in Access these are my thoughts...

Relationships perform two functions.
First, and less important, is that when you add two tables to a query grid, the tables adopt any relationship that has been set up for those two tables.
If you do not enforce RI in a relationship, this is the only function the relationship performs.

Second, and when you do enforce RI, the relationship acts to ensure the integrity of the data in related tables.  So you can't create orders for customers that don't exist, or accidentally delete a customer where that  customer has orders.  
This IS very important.  
The constraints can be implemented in code but  the collection  of database tables should really be thought of as a separate entity in its own right - and irrespective of which frontend application is used to access the data, the integrity of the data must ensured as afar as possible.
For example, if you don't set up a solid relationship with RI then you are allowing someone with say, Excel, to connect to your tables and do anything they like to the data.
The cascade settings can be seen as adding to the integrity of the data but also potentially dangerous if the developer is not fully aware of the settings in force.
In general, I would usually set the Cascade Update for a one-many relationship.
I rarely set the cascade delete option, it would normally only be for tables which hold data temporarily while its on its way somewhere else.  But it has not been my practice to allow deletion of important data - I give the user a 'delete' button which just sets a 'deleted' flag in the record and/or periodically move stuff into archive tables.
0
 
jjafferrConnect With a Mentor Commented:
LOL

Normally, I don't link Tables, unless there is a need for it.
Linking tables have a lot of advantages, indexing of Foreign keys is one of them.

jaffer
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Enforcing referential integrity should generally be left up to the database engine, since it generally is much more thorough in these matters than are mere mortals :)

That said, it's certainly not uncommon to leave out the relationships and handle things on your own.

As an aside:

Do you have multiple tables named "Legend Cat XX", and "tblSRStorage_XX" where "XX" is a number? It's impossible to determine this by looking at the relationship dialog (Access can add the same table multiple times, and will use a number to indicate those). Having identical tables with only the name being different generally indicates troubles with your database schema ...
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
I estabilish relationships, but don't enforce RI on anything typically.

I do this mainly as a form for documentation on the database design.

Jim.
0
 
developingprogrammerAuthor Commented:
fantastic stuff guys and once again thanks for all your responses and more importantly all your kind guidance, it's really so unbelievable that i can receive such sincere and kind help. thanks all so much. let me leave this question open awhile longer so that others can chip in and when readers search this next time they can benefit from all our sharing = )
0
 
developingprogrammerAuthor Commented:
thanks for your help and guidance guys!! = ))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.