I just wanted to clarify my question regarding the 4NF car example we were discussing in class the other day. In the table, there were 3 fields: car type, color, and engine. Looking at the data given, you would need all three of these characteristics in order to uniquely identify a car. Since keeping everything together in one table poses some problems in regards to deletion, insertion, and update anomalies, we determined in class that you would break the table up into two parts, with car type as the recurring key in both tables: [car type] => [color] and [car type] => [engine]. I understand that color and engine are both attributes of car type and that is why it is the primary key. But, hypothetically speaking, if you were running a paint shop and paint color was your primary concern, couldn’t you structure the tables so as to make color the recurring/primary key? [color] => [car type] and [color] => [engine]
This probably isn’t the best example unless there is some crazy rule that states cars with certain engines can only be painted certain colors. But, the real question I was trying to drive at is whether or not there will be situations when there will be multiple field options that can be chosen as the primary or recurring key. For example, if the three fields were car type, paint color, and trim color, couldn’t you structure it either as [car type] => [paint color]; [car type] => [trim color] OR as [paint color] => [car type]; [paint color] => [trim color]? (assuming that certain trim colors only go with certain paint colors and certain car types only allow certain trim colors… i.e. Eddie Bauer vs. Regular Explorers?)
Comments