Is Learning about relationships in Access necessary? (1 Viewer)

[Isaac]

Pat, I use relationships constructively and want them to be present. So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations. Granted, the more complex the problem, the more likely it will be that you really DO need relationships., and thus I definitely favor having them. But until you learn WHY you want to normalize your DB, you won't grasp relationships completely. I wouldn't bear down too hard on Isaac because I remember a time (dimly) when I didn't bother with relationships that much either. OR I created spot relationships in the upper portion of the query design grid. Not system-wide, but localized relationships. And Access is powerful enough to work on that basis until you DO eventually learn better.

Not all of us have databases that require Boyce-Codd normalization or 5th-normal form normalization. Not all of us have to manage 15-way JOINs (though the Navy DID have that with their personnel system).

I don't even think it has that much to do with complex vs. not complex. If you feel you will benefit from cascade deletes (etc), then go for it. If not, it's a nice visual tool to show others how your database works. And that's it.

 

[The_Doc_Man]

Well, there is more to it than cascade deletes. RI ALSO prevents you from creating a fatherless child record. But if you program that so that (a) you always create records through a form and (b) you always create them in a way to assure parentage, then things will work OK anyway. UNTIL you try to do something that involves a parent/child linkage.

For those people who use Access to manage lists, maybe there AREN'T any such relationships. However, when a newbie graduates to a true case of parent/child data, then is when relationships start to shine brightly. But you have to remember that I'm a pragmatist. When it comes to having relationships, you don't need them until you need them. (No, not intended as a tautology.)

@khodor - as you can tell, some of us feel more strongly on the subject than others. But then, there is that old rule of scientists: You can take all of the experts in the world for a given subject and lay them end to end but they still aren't guaranteed to reach a conclusion.

 

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

 

[Pat Hartman]

@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 and the enforced RI will prevent that delete but would allow a state to be deleted if there were no 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.

 

[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

 

[Pat Hartman]

I started with big iron and learned my defensive programming skills in the world of the mainframe. They work in Access and every other platform just as well. Neatness counts. Keep your workspace "clean" and keep your car mirrors properly aligned so you can see what is around you

 

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

 

[Pat Hartman]

you are still using the implicit relationships within your data when you use joins

I can join CustomerID to EmployeeID in the Employee table but I'm not sure how well that will work if I'm trying to get Customer name. I can also join CustomerName to EmployeeLast but I probably won't get any rows returned. You can join any two fields of like datatype. You usually get nonsense returned but you can still do it.

I'm sure I think about this differently than some of you do because I learned my craft from professionals. I did teach myself Access but learning a new platform once you already know how to code is akin to learning a second language. You already learned a lot about the syntax of your native language when you learned your first foreign language and that gives you a heads up with others, especially if they are in the same family and so have similar syntax.