Relationships - Your thoughts ? (1 Viewer)

[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.

 

[Pat Hartman]

Relationships are defined in the relationships window. Relationships are used to enforce Referential Integrity. They prevent orphan records for example. They can also be used to cascade deletes or prevent deletes. For example, a typical order entry application has
Customers --> Orders --> OrderDetails <-- Products
OrderDetails is a junction table between Orders and Products. I would always specify Cascade delete on the relationship between Orders and OrderDetails because if deleting orders is allowed, then I want all children of an order deleted automatically if I delete an order. Customers are rarely deleted. By not specifying Cascade delete on the relationship between Customer and Orders, the database engine will PREVENT you from deleting a Customer if that customer has any orders provided you have checked the enforce RI box.

Joins in a query are not relationships. You can join any two tables/queries on any columns of like data type. So you can join Customers to Vendors on Customer.Address to Vendor.CompanyName because Address and CompanyName are text in both tables. The join doesn't make any sense but the query engine doesn't make judgement. It just does what you ask. You will frequently do strange joins when you get data from other sources and have to attempt to match it up with data in your application.

As someone already mentioned, Access creates HIDDEN indexes on foreign keys when you define a relationship in the relationship window. One side of a relationship is ALWAYS a primary key (or in rare cases, a unique index). So in tblCustomers.CustomerID --> tblOrders.CustomerID, it is tblOrders.CustomerID that is the foreign key and that is the column on which Access will build the index to help facilitate the join.

Access also has a default which you should remove and that is the auto index on fields with specific suffixes. This is intended to help people who don't know anything about databases to ensure that at least some indexes exist. However, if you know what you are doing, this "feature" will just create extraneous and even duplicate indexes so turn it off.

 

[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.

 

[Pat Hartman]

It's in the File/Options section. It's on the Object Designers tab in the table design view section. Remove the contents of AutoIndex on Import/Create and then clean up the mess the setting made on ALL your applications..