Defining relationships

[stevekos07]

Just a question. How important is it to define table relationships in the Table Relationships area? I often find that for individual object purposes I change the relationships anyway to fit the requirements of the particular query I am building. It seems that establishing formal relationships isn't always necessary. Am I way off track here?

 

[jdraw]

Define your table relationships based on your business facts.
You can change "joins" for queries independently.

It seems that establishing formal relationships isn't always necessary. Am I way off track here?

In my view this sort of suggestion would be grouped with --do I really have to normalize my data? Some situations force certain decisions which may violate "established practices".

Relational database is built on some basic tenets and products/databases work best when these are followed. But you won't be the first to ignore relationships etc.

Good luck.

 

[MarkK]

Steve, the main advantage in the relationships window is that you can easily define relationships between your tables that enforce "referential integrity." When referential integrity is enforced on a parent/child relationship between two tables, it is impossible to add a row to the child table that doesn't link to a valid parent row. It is also impossible to delete a parent row without deleting the child rows. This is an important relationship to be able to define.

 

[HiTechCoach]

Steve,

I find that having the Database Engine Enforce Referential Integrity to be very important. As MarkK pointed out, this is set when you define the relationship.

 

[stevekos07]

Thanks for that. Yes, I was wondering if it is the referential integrity aspect that was the important reason for establishing standard relationships. I hasten to say that I DO use the relationship builder for all tables that I think will need to be related.

So, what changes then if tables are combined in purpose-built queries within particular objects? Do these break or affect referential integrity?

 

[HiTechCoach]

Referential Integrity (RI) is enforced by the ACE/JET (the Access database engine). There is NO way to get around it.

Add or updating records must follow the RI rules that have been defined. It does not make how yu are adding or changing the records. RI will be enforced is defined.

A SELECT query is only reading data. You can define any join you want. It will not violate any defined RI.

 

[The_Doc_Man]

The main reasons I used table-level relationships:

1. Establish RI. That has been addressed already so for once I won't beat a dead horse.

2. While it is true that I can spot-override any extant relationship in the query design view, when I don't want to, the various wizards acknowledge them automatically. Including some that I coded so long ago that I had forgotten about them. But then, I had a complex relationship diagram with 50 component tables. Granted, some were there only because a table was a "code lookup" case, a translator linkage of sorts. Some were there because I was using RI to prevent someone from trying to enter an invalid code, and the combo-box wizard honors relationships too!

Basically, having a predefined set of relationships makes the wizards look smarter.

 

[stevekos07]

Thanks for that. Another good reason. Yes, I find that my relationship diagram is a bit onerous to interpret now. It would be good to be able to subdivide the diagram into different views that could help to organise and track relationships a bit better. Is this possible?