I'm normalising a database for my company and one pattern I'm seeing a lot is using a domain lookup table for a value but also allowing "Other" and storing the results in a separate column.
Is there a cleaner way of representing this?
I'm following the normal forms up to 5NF and domain key form. In some tables this pattern is repeated more than once 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).
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.