Solved

relationship table

Posted on 2013-05-28
6
449 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now