no relationships at database level

[Tim Zych]

What do you all think about not setting up any
relationships at the database level, but instead setting
up relationships in queries "on the fly" as needed?

What are the pros / cons of that approach?

Thanks for any input.

 

[Tim Ferguson]

What are the pros / cons of that approach?

Data integrity.


Tim F

 

[Andrew Smith]

Cons:

- you won't be able enforce referential integrity
- fields involved in relationships will not be automatically indexed
- you won't be able to use cascading updates if you change the value of a
primary key used in a relationship, so you risk losing the connection to all
the related records (not that you should need to do this if you use a
primary key that has no meaning to the user).
- you won't be able to use cascading deletes, so you can delete a record on
the one side of a join and create several orphan records on the many side
- the forms and reports wizards won't work
- when you realise that it is a problem you will have to spend hours
cleaning up your data so that you can create the relationships that should
have been there from the start

Pros
- you will have total freedom to create a useless mess

 

[Tim Zych]

Along the lines of what I suspected. Thanks for the input.
In case you are wondering, I am not approaching a database
design this way. I am wondering about another database
design I saw.