?
Solved

relationship table

Posted on 2013-05-28
6
Medium Priority
?
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 400 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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 800 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 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