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?

 

[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

 

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