Empty Columns / Fields – Normalization

[Navyguy]

Empty Columns / Fields – Normalization

Looking for some advice here; I have several tables that have a similar issue that I am not sure really is an issue. I just might be trying to over complicate it.

Each table has a primary key and then several “place holders” that may be used. The table(s) is normalized to the point where it may not be related to every possible record, but if it is related, there will certainly be several blank fields in every row.

In this example, the table is for keeping a record of PLC wire connections that are attached to a common / stock relay. The relay has 10 possible connection points, but generally only four are used thus leaving 6 “place holders”. They might be used in the future, but unlikely. In addition different records will have different configuration of connection points.

So right now I have

Tbl_Relay
Relay_ID – PK - Autonumber
Equipment_Number – FK
Relay_Contact_1
Relay_Contact_2
Relay_Contact_3... to Relay_Contact_10

The data (wire numbers) in each Relay_Contact_# field is a alpha / numeric such DO:01.01.13.03A

I thought about flipping the context around and making the wire numbers the table and just assigning the Relay_Contact_#, but it is the wire numbers I am collecting... akin to making a table with telephone numbers so you don’t have blank fields for other telephone options like 1-800, business, residence, mobile, fax, etc.

The Equipment_Number is the unique number for each series of items; this number is connected to maintenance, additional equipment like motors, spare parts list, etc. Some Equipment_Numbers don’t have a relay, but will have a similar situation with other tables Tbl_Control_Wiring or Tbl_Button_Wiring where there are a number of options but the wires are only connected to a limited number of points.

Am I making this more complicated than it needs to be?

Cheers

 

[MarkK]

This should be done with two tables tRelay and tContact. Then, if a thing exists there is a record for it, and if it doesn't exist, there isn't.

 

[Navyguy]

Hi MarkK

Thanks for the quick reply... so to plot this out I would have:

Tbl_Relay
Relay_ID – PK – Autonumber
Equipment_ID - FK
Relay_Number (I am not sure what I would call it; maybe just a yes / no?)

Tbl_Contact
Contact_ID – PK – Autonumber
Relay_ID – FK
Contact_Number (select from 1 to 10 I guess)
Wire_Number (the PLC wire DO:01.03.11A or whatever)

One-to-many from Tbl_Relay to Tbl_Contact on Relay_ID

Does that show what you were thinking? Seems simple enough, just needed someone to turn my head a little bit to see through the weeds...

Cheers

 

[MarkK]

Yeah, exactly, except what is the origin of the tblContact.ContactNumber? Is that just a number you make up, or is it a number that exists in the real-world?
If only the former, omit it. If it exists in the real-world, record it.
Hope this helps,

 

[Navyguy]

Hi MarkK

Thanks again.

Tbl_Contact.Contact_Number would be the actual relay terminal number (1-10) that the wire is landed on.

What does not exist is the Tbl_Relay.Relay_Number so not 100% sure what I would do there. Perhaps like I thought maybe a yes/no, but I am not really a fan of yes / no. It is too easy to be omitted (not that there is a lot of them).

Cheers

 

[MarkK]

If there is no important reason to have a thing, omit it. If RelayNumber is not useful and does not exist in the world, delete it from the table.