relationship or no relationships

[SidCharming]

Should be a simple answer, but I am wonding why some databases I am working on that are not mine don't have relationships built. Each time I pull in two tables into a query I have to build a relationship.

What is the reason to:
A. not have a relationship at the table level?

B. expect the user(me) to create a relationship each time I want to build a query?



Sid from Minnesota

 

[Golom]

A. Lack of knowledge or lazyness.

B. Because only you know what the query you are building is expected to do. You don't necessarily always join tables on the fields defined in a foreign key relationship.

 

[BNPMike]

A The relational model does not define relationships. So in Access they are inessential and in other relational dbmss may not be present at all.

B None of my current Access apps have relationships. I am happier to draw joins each time (0.7 seconds) than have Access put in joins I may not want.

I accept they are probably a good thing, but by no means de rigeur. My biggest gripe is new un-trained Access users think they do more than they actually do and thus become confused by why Access is not behaving in what they see as a logical way.

 

[DanJR]

A The relational model does not define relationships. So in Access they are inessential ....

I don't believe this is true. I would look at it from a different perspective, that domain, primary key, and foreign key constriants are considered an important and fundamental part of the relational model. Within Access, the only way i know how to express foreign key constraints is by creating an Access "Relationship". Using DDL SQL to create foreign key contraints will also result in an Access "Relationship". Thus, depending on your relational model, "relationships" may be essential.

 

[SidCharming]

Thank you for your responses... I see that it is somewhat a gray area of discussion. But from my perspective I would lean towards the idea that relationships are important (if used properly).



Sid from Minnesota

 

[BobJacksonNCI]

Uh,

What about referential integrity?

That IS a question, not a statement. Regarding cascading deletes, I'd think it would be easy to overlook when each query is DIY versus settings automatically propagated.



HTH,
Bob

 

[BNPMike]

Referential integrity and cascading deletes are purely to stop people entering crap data. To be sure, that is a feature of human behaviour but you have to understand there are a million other ways people can corrupt your data. Indeed this week I had a case where one of the world's top banking groups had a senior person entering 6 million instead of 60,000. And that was in a fully validated commercial finance package.

Referential integrity is fine, but not essential. You can pick up errors of that limited type by periodically running simple queries to list orphans.