I'm working on redesigning a database of the "technically works but doesn't really work the way a database should" variety. I think that, at some point, my predecessor just gave up and crammed everything into one table. I'm trying to correct that.
Some background information: Our product is customized to order. A set of customized options is referred to as a "Specification" and anywhere from 5-20 different components can be selected. Not all components are applicable for every spec.
What I can't decide on is how far it really needs to be corrected. If I break out every component into its own table, I'm going to end up with a lot of tables that have <5 entries, some of which will consists of little more than an ID field and one additional information field. It is unlikely that any scalability will ever be needed for these properties, either.
As an example, let's say the product is... shoes. I have a ShoeSpec table consisting of (SpecID, CustomerID, HeelID, LaceID, price). In reality, this is a much more involved product with a lot more traits per spec, but this should work for the sake of example. SpecID and CustomerID are both keys for a spec, as the price is dependent on both.
I would then have the Lace table(LaceID, Lacetype), and Heel Table (HeelID,HeelType).
The only data that will ever be in the lace table, though is
ID|Type
1|Laceless
2|Round
3|Flat
And for heels
ID|Type
1|Stiletto
2|Flat
Question 1: How necessary and desirable is it to break all of these out like that, given their static nature and the limited amount of data? I feel like, even though this may be the proper way to do it, I'm making a mountain out of a mole hill.
Question 2: Keeping with my shoe example, but adding one further degree of complexity, suppose I wanted to add aglets (that plastic bit on the end of a shoe lace) to the specification. I can see two ways to go about this:
In both, I would first create an Aglet table (AgletID, color).
What I'm not sure on after that is how to include that into a specification. An aglet, being ultimately a trait of a shoelace, could be added to the lace table, which would become: (LaceID, Lacetype,AgletID)
Alternatively, I could add it directly to the specification table, which would now be (SpecID, CustomerID, HeelID, LaceID, AgletID, price)
Finally, I could create a third table, ShoeLaceSpec(SLSID, LaceID, AgletID), which would then contain the assorted combinations of laces and aglets.
If this were done, the Spec Table would now look like (SpecID, CustomerID, HeelID, SLSID, price).
Basically, in this case, would it be best to make aglets a linked property of laces, make it just another aspect of the final product specification, or would it be best to combine the two into a sub-specification that is then used in place of either in the final product spec?
Thoughts, advice, input? Am I overthinking this? I don't see any reason that any of the above wouldn't necessarily work, but I feel like there is probably an objectively correct way to go about it, and I'm partial to doing it that way.