Solved

relationship table

Posted on 2013-05-28
6
484 Views
Last Modified: 2013-06-19
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.
0
Comment
Question by:developingprogrammer
6 Comments
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 100 total points
ID: 39200499
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 39200755
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
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 39200800
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 39201011
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
 

Author Comment

by:developingprogrammer
ID: 39201704
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
 

Author Comment

by:developingprogrammer
ID: 39261306
thanks for your help and guidance guys!! = ))
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question