Where to define relationships?

[wvandenberg]

How do I know when I need to establish a relationship in the relationship window or if doing an ad hoc join in the query design window is sufficient?

Do I need to establish the relationship in the relationship window only if wish to enforce integrity?

Thanks,
Wendy

 

[dhookom]

It's good practice to create your relationships and enforce integrity on your tables. This will create default joins in your queries.

You don't have to create any relationships if you don't want to.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wvandenberg]

Are there any general rules about when to enforce integrity? It would seem to me that you should always try to enforce integrity.

Wendy

 

[dhookom]

I will often not set referential integrity when I am creating a new application and need to import data from other sources. Once I get my application ready for public consumption, I make sure referential integrity is set where needed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BNPMike]

Personally I never define relationships. It's sound theory but I just don't like bureacracy. If you built a finished product then I guess there could be no harm in battening down the model with referential integrity but that never seems to happen to me-and-Access.

 

[Crowley16]

if you don't define referential integrity, then when you insert or delete or modify data in one table, then you have to make sure you are catering for all the related records in all other tables.

if you designed the database, and the database is quite simple, then this isn't a problem. However if someone else is acting as the dba and they don't have a full knowledge of all the relationships, or if the database structure is very complicated, then it's likely something will get missed and you end up with dodgy data.

--------------------
Procrastinate Now!

 

[PHV]

In the real life (ie at job, not dealing with msaccess at all) I always enforce referential integrity, never use cascading delete nor update, and create as much triggers as possible.

 

[rac2]

PHV,

Why do you not use cascading deletes or updates? Is that just so, or have you found issues when using them? It sounds as though you prefer triggers to these builtin features for maintaining integrity.

For my part, I am the primary maintainer of a moderately complex application, I know all the tables and relationships. I never create referential constraints, never use cascading deletes. Just because it is easier to let it go. Everyone who uses this application is in the same building, when there is an issue, they just tell me. In five years, I cant recall any fiasco results.

None-the-less, I always thought that those would be important things to do in a commercial application.

 

[PHV]

rac2,
I don't use cascading updates as, for me, if a PK value may change then the column(s) is not candidate.

I don't use cascading deletes just so (call it paranoia )

I heavily use referential (and check) constraints and triggers just to bullet proof ([small]not sure it's the right expression[/small]) the DB.