Is Learning about relationships in Access necessary?

[Galaxiom]

While forms can have all kinds of facilities to prevent bad data being accepted, best practice is to always protect the database from storing incoherent data right down at the table level. Data can be inserted and updated directly to the table by people trying to restore things or make corrections. However competent they may be, anyone can make a mistake.

I've quoted myself here.

Many developers don't realise that the difference between a good application and an outstanding application is less about how works than how it fails when confronted with the unexpected. Embedding protection of the foundations in the backend tables is a smart move.

Anyway setting up relationships is a long way from being a complex aspect of database design and takes hardly any time. Moreover, that time is saved when building queries because they automatically create the joins. Really there is no reason not to do it.

Be aware that not having relationships set looks unprofessional to many observers. Though I wouldn't consider it a mortal sin if the rest of the database is well constructed.

BTW I have never used a Cascade Delete and am unlike to.

 

[Isaac]

@Isaac, I didn't mean to offend you. You offer a lot of great advice and are very helpful and nice too. But, relationships should be enforced by the database engine whenever possible, not in code. When the database engine enforces the relationships, they are ALWAYS enforced. There can be no accidents or places where you missed something. What if at some point the BE needs to be shared by another app, how are you going to control the rules then? Unless it is you writing the new app, you can't but the database engine can.

There are relationship rules that cannot be enforced by the database engine because they are circular. For example, 1-m relationships allow 1-0 and if your business rule does not allow that, the engine cannot help you because you can't create the m-side before you create the 1-side and the 1-side is not supposed to be allowed without at least 1 m-side record. This you would need to implement in code. I do it by adding an "Incomplete" flag to the 1-side record. That prevents it from being used for anything if it is marked incomplete. Then either in code or with a trigger, you can force the 1-side flag to be updated whenever a m-side record is added and you can prevent the last m-side record from being deleted. This is pretty complex and I've only had to implement it ONCE in all the years I've been developing database apps which is more than 40 years. Long before Access was a gleam in Bill's eye, I was building applications using DB2, the IBM relational database.

Without relationships, you also loose the option of Cascade Delete. Granted, not everyone understands when this options should be used but when you do, it is a timesaver and a way to ensure that a relationship is enforced. This option is only used when the relationship is an actual dependency and if you delete the 1-side, it makes no sense to keep the many-side records. An example is an order entry relationship. The orderdetails don't have any meaning unless they are related to an order so if your business logic allows deleting orders, deleting the order should automatically delete the orderdetails. However a relationship like Customer-State would never use cascade delete. First of all, it makes no sense to allow states to be deleted and if you did come to that, you would never want to delete a state that had related orders. However, your business rules may not require an address for the customer if you are not shipping the order so both the 1-sice (state) and many-side (order) have an independent existance.

Galaxiom said the magic word - protection.

Lots of best practice is actually defensive programming. You have to anticipate the places where the app could or will change in the future and make accommodations now, to prevent major changes later. You have to anticipate user errors and help them avoid them. For example, EVERY SINGLE date field should have validation logic. Why? Because 1/1/901 is a valid date. It makes no sense in the context of most applications but as far as Access and the database engine are concerned, it is valid. Dates of Birth cannot be in the future, etc. Some simple rules can be defined at the table but many rules are too complex and have to be defined in the data entry form. A rule such as <= Date() can be defined at the table for DOB fields. In this case, the constraint is that only functions known to SQL can ever be used in validation fields. If you need some other logic, you have to code it yourself.

Fair enough - understood. I'm used to embracing server-side constraints in SQL Server context, but not so much in Access, frankly. I can see there is plenty good to it. I've always been happier keeping my table designs extremely simple in Access, and so perhaps I've overdone that concept a bit

 

[khodor]

Thanks for you all, For your Help and Your Time

 

[gemma-the-husky]

I am rather late to this. I think "learning about relationships" in principle is wholly good. Even if you decide not to assert relationships in a database, you are still using the implicit relationships within your data when you use joins. You can join anything of the same data type, but it makes no sense unless the fields you are joining have a real relationship/correspondence. Asserting a relationship merely makes it explicit, not implicit.