Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

relationship table

Posted on 2013-05-28
6
Medium Priority
?
504 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
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.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 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