Relationships - Your thoughts ?

[ECEK]

I rarely build my databases with relationships specified choosing to make the relevant relationships within forms or querys.

Am I committing a heinous crime ?


Can I get your thoughts please ?

 

[isladogs]

Setting the links in the Relationships window means that you only need to do it once rather than in each query etc. More importantly it also allows you to setup referential integrity with cascade update/delete so tables remain synchronised.

Note that if you do that, you can still modify the links in queries for specific purposes.

 

[ECEK]

Thanks Ridders. Your last line was really useful to know.

Will a database run quicker over a network with pre-defined relationships or is it much of a muchness?

 

[gemma-the-husky]

If I may insert a thought, I think what makes it run as quick as it can is that Access automatically indexes the fields used in a relationship. I am not sure without checking whether it's both the 1-side and the many-side, or just the many side, but it means a query can read the linked data as efficiently as possible. These indexes are hidden - there is a "hidden" flag in the index properties - which prevents users messing around with them, but they are still available if required, and can be inspected programmatically.

 

[isladogs]

Dave has made a very good point about indexing.

Of course, you can add indexes yourself. Doing so to fields often used in search criteria usually significantly reduces the time taken (depending on the datatype).

However, indexing significantly increases the time taken to run updates on the same fields. In a recent test, I ran update queries on a single field in a large table of around 2.6 million records. The time taken was around 40 seconds before indexing and over 60 seconds after indexing.

 

[The_Doc_Man]

Darn! Didn't know about the auto-index based on suffixes. I usually roll my own anyway. I'll have to look into that one so I know where to turn it off in the future.