Database Design Question: Separating Hyperlinks for Equipment and Panels

[JonWeisbarth]

Hello,

I am currently working on a Microsoft Access database, and I have a design question that I'd like some input on. The database contains information about electrical equipment and panels, and I have a table called "hyperlinks" which stores links related to these items.

In the "hyperlinks" table, I have two foreign keys: "equipment ID" and "panel ID." The purpose of these foreign keys is to store links relevant to either a specific piece of equipment or a specific panel. However, only one of these foreign keys is populated at any given time because the database is not set up to view both panels and equipment simultaneously.

I am considering the structure of this table in the context of database normalization, which involves the three normal forms (1NF, 2NF, and 3NF). Currently, I have ensured that the table is in 1NF as each column holds atomic values. However, I'm concerned about 2NF and 3NF.

The question I have is whether it's advisable to keep this current structure with both "equipment ID" and "panel ID" in one table or if I should create separate tables for "equipment hyperlinks" and "panel hyperlinks."

I would appreciate any guidance or experiences you can share in this matter. Thank you in advance for your assistance!

 

[CJ_London]

Why not just include the hyperlink in the equipment or panel table?

 

[Pat Hartman]

You might want to start by fixing your column and table names. Names should NEVER include special characters or embedded spaces. The characters should ONLY be 0-9. a-z. A-Z. amd the underscore. NO other characters are acceptable to VBA and therefore force you to enclose your non-conforming object names with square brackets - []. Just adds a lot of noise and gets confused with any parentheses.

Also, naming all your primary keys "ID" is really poor practice. It forces everyone except possibly you to look at the relationship diagram in order to have a shot at figuring out where the FK might point. If you want to distinguish the EmployeeID's use as a FK from its use as a PK, add an FK suffix to the FK usage. ALWAYS be consistent with these pairs of names. There will never be a conflict in a query because you would only include the FK in the select clause. You would not include both. The only time there would be a problem is if you have multiple references to the same PK in the same table. For example, if you have both Billing and Shipping address in the Order table, you will need ShipStateIDFK and BillStateIDFK.

 

[JonWeisbarth]

Why not just include the hyperlink in the equipment or panel table?

because we need a one to many relationship. Equipment/Panel can have many hyperlinks. You suggestion would limit us to a single hyperlink.

 

[Bgum]

You might want to start by fixing your column and table names. Names should NEVER include special characters or embedded spaces. The characters should ONLY be 0-9. a-z. A-Z. amd the underscore. NO other characters are acceptable to VBA and therefore force you to enclose your non-conforming object names with square brackets - []. Just adds a lot of noise and gets confused with any parentheses.

Also, naming all your primary keys "ID" is really poor practice. It forces everyone except possibly you to look at the relationship diagram in order to have a shot at figuring out where the FK might point. If you want to distinguish the EmployeeID's use as a FK from its use as a PK, add an FK suffix to the FK usage. ALWAYS be consistent with these pairs of names. There will never be a conflict in a query because you would only include the FK in the select clause. You would not include both. The only time there would be a problem is if you have multiple references to the same PK in the same table. For example, if you have both Billing and Shipping address in the Order table, you will need ShipStateIDFK and BillStateIDFK.

Pat, thank you for the suggestions. I originally designed the database that the OP asked about. It was my first database design and I later learned about naming best practices too far into the design. Changing field names meant I had to refactor VBA field names as well. I appreciate your recommendations.

This is the first time I have received this feedback on the IDs. I didn't realize this was problematic. Thank you for teaching us. I followed everything you said except for the last two sentences. While your recommendations were very helpful, I don't think they addressed the OP's question. Correct me if I am wrong.
To restate OP's question: We have two tables, Equipment and Panels, that both need a one to many relationship to a hyperlink table(this being the many side). They both need to have a one to many relationship to a Hyperlink table. We could make two identical Hyperlink tables. One that store the FK for equipment, the other stores FK for Panel. We thought this seemed redundant, Thus we made the Hyperlink table with two FK's, where only one would be populated. In the hyperlinks table the OP made two FK fields. One for the Equipment_ID_FK and another for the Panel_ID_FK. For any record within the hyperlink table, it would have a relationship to either the Equipment Table or the Panel table, but never both. Right? Because we are storing a related record for one of the two. We are worried this is a bad practice and is breaking database normalization. This question stems from a lack of fundamental understanding. We are hoping someone can help us better understand this problem and correct our fundamental understanding.

This picture of one of the forms may help provide more context:

Thank you for helping us.

 

[CJ_London]

because we need a one to many relationship. Equipment/Panel can have many hyperlinks.

Ok - wasn’t clear from your first post.

to answer your question- there is a phrase ‘normalise until it hurts, then denormalise until it works’. two hyperlink tables would be more normalised but involves additional work

or you could have a single entity Id and another field to indicate the type of entity - but then you cannot enforce referential integrity

or you can go the whole ‘entity’ management structure

personally I would stick with your current method, providing the two FK fields have a default of null so you can enforce referential integrity and you do not have the situation where the same hyperlink needs to be linked to equipment and panels - tho of course you can duplicate the hyperlinks

 

[Pat Hartman]

I didn't realize this was problematic.

Access doesn't care what the names are. It is humans who care. I've been doing software development for a lifetime. I learned a very long time ago that I used far fewer brain cells when writing and modifying applications if I didn't have to work so hard to figure out variable and object names. That is the problem - human bandwidth rather than a computer issue.

Everyone acquires a "style" over time. Some people would have made great programmers in the 60's when file, column, and variable names were all limited to 8 characters comprised of only A-Z and 0-9. Those people abbreviate to such a level that only a domain expert has a shot at figuring out what a name refers to and even those will have trouble. The other end of the spectrum are power users who have no problem today creating object and variable names as long as Windows and Access will tolerate. There is a happy medium where the names are not unbearably long nor so abbreviated that they make no sense to anyone except the author.

My "style" is CamelCase with the occasional underscore for emphasis. Numbers are even more rare. Numbers in object names are a symptom of improper normalization. Certain field names are always abbreviated and always the same way. Date is always DT as a suffix. So, SaleDT, BirthDT, etc. Customer is always Cust, never Cst or spelled out. Percent is pct. Employee is Emp. There are probably a couple hundred or so English words that are very commonly used that also have fairly standard abbreviations. If you use other abbreviations that are specific to your environment, you might want to create a dictionary to help yourself and anyone who comes after you.

I followed everything you said except for the last two sentences.

They have nothing to do with this application. They were merely an example of when you might have a problem implementing my "matching name" suggestion.

I don't think they addressed the OP's question. Correct me if I am wrong.

They don't. The previous poster answered the question. I was just carrying on with the ongoing normalization process.

Equipment and Panels, that both need a one to many relationship to a hyperlink table(this being the many side).

You don't need two hyperlink tables as CJ pointed out. The relevant hyperlink belongs in the Equipment and Panels table in the relevant row.
Given the additional information that there is an actual 1-m relationship with hyperlink, then the best solution is to use two tables. WHY? If you don't, you CANNOT enforce RI and RI is much more important than any savings you think you might have if you store all hyperlinks in the same table with separate FK's. Using separate tables is clean and avoids having to have table maintenance logic embedded in your application code to ensure that only one or the other FK has a non-null value.

PS - you have NOT enabled RI for all relationships. Every relationship needs RI.

And last but not least:

It was my first database design and I later learned about naming best practices too far into the design. Changing field names meant I had to refactor VBA field names as well. I appreciate your recommendations.

The sooner you fix a problem the less work the fix entails. Now, you have another installation and another branch of your initial design that is propagating the same poor naming practices. It never ends until you fix it. Access can help you with some of the changes by using Name Auto Correct. Just be aware that this is a truly dangerous "feature". It is best left off and only turned on when you need to use it and then turned off again once you are done. In order to not slice an artery and bleed to death, you need to understand something of how it works. Then what this tool can't do, you can do with MZ tools or some other tool that has find and replace capability. Just remember that find and replace is also dangerous. It is a global change so you have to use care with it so that you don't accidentally change something that shouldn't be changed. Depending on how much code, etc that needs to be cleaned up, you need to set aside a few days so you can do it with care and save and test very carefully inch by inch until the whole mess has been cleaned up. It is not trivial but it is mostly just tedious and requires attention to detail.

 

[June7]

I had a similar dilemma (comments instead of hyperlinks). Went with 3 separate comment tables. Rejected idea of 1 table with 1 FK field and a category field. Did not think of 3 mutually exclusive fields in one comment table but could probably have made that work.

Side note: I don't use Hyperlink type field. Just a text field then format as hyperlink when needed.

 

[Bgum]

Thank you to everyone that weighed in. This has been very beneficial in our development. Thank you for your time. You are much appreciated.
Case closed!

 

[Pat Hartman]

You're welcome.