I’m currently in the process of normalising a database for my company and one pattern I’m seeing a lot in this database is using a domain lookup table for a value but also allowing “Other” and storing the results in a separate column.
My question is just whether there is perhaps a cleaner way of representing this? For context I’m following the normal forms up to 5NF and the domain key forms. The bigger issue I’m seeing is in some tables this pattern is repeated more than one so we have a table like the following:
╔══════════════╦══════════════════╦═════════════════════╦═══════════════════╦══════════════════════╗
║ appliance_id ║ location_type_id ║ other_location_type ║ appliance_type_id ║ other_appliance_type ║
╠══════════════╬══════════════════╬═════════════════════╬═══════════════════╬══════════════════════╣
║ 123 ║ 1 ║ {null} ║ 13 ║ Freestanding Boiler ║
║ 124 ║ 13 ║ Annex ║ 1 ║ {null} ║
╚══════════════╩══════════════════╩═════════════════════╩═══════════════════╩══════════════════════╝
Where for example, a location_type_id & appliance_type_id of 13 is “Other” in the relevant lookup tables.
So for example, the location type table looks something like this:
╔═════╦═══════════════╗ ║ id ║ location_type ║ ╠═════╬═══════════════╣ ║ 1 ║ Living Room ║ ║ 2 ║ Kitchen ║ ║ ... ║ ... ║ ║ 13 ║ Other ║ ╚═════╩═══════════════╝
This might very well be the best possible solution (although I would probably separate the location and appliance type into different tables) although I would like to get some more experienced eyes on this, this is the first time I’ve tackled a complete top-to-bottom restructure and I’m keen to get it right from the off.
Thanks!
Advertisement
Answer
After talking it over, we’re gonna do as suggested and do a text scan and use that to populate our lookups, then going forward we’ll try to discourage the use of free-text fields for lookups, storing the values in a separate table for the time being so we don’t clutter our main tables.