Ok..I want to get to form on the practical application of the First Normal Form (1NF).
Seems to me that one of a basic definitions of 1NF is: Make a separate table for each set of related attributes, and give each table a primary key (to eliminate duplicate records).
The aim here is to avoid redundant information and overcomplicating query code.
My problem is that I have a PHP interface which is quite tightly coupled to the MySQL databse in how it presents the data/.csv-downloads. At the moment, it's proving rather hard to work out how to change this and I was wondering if one could bend the 1NF rule a bit until I get better at understanding SQL & PHP? Yes - not ideal from a database design point of view but I wonder if the problems that come from not strictly adhering to 1NF always apply? Allow me to elaborate in an example...
Let's say we have a simple little database to record if a car has worn tires & where:
Table: check_my_tires
----------------------
pktire_wear (primary key)
left_front_tire_wear (NOT NULL and takes only the values* "Worn" and "Normal")
right_front_tire_wear (NOT NULL and takes only the values* "Worn" and "Normal")
left_back_tire_wear (NOT NULL and takes only the values* "Worn" and "Normal")
right_back_tire_wear (NOT NULL and takes only the values* "Worn" and "Normal")
*default value = "Normal", acceptance of only "Worn" and "Normal" enforced at the UI level via a listbox which only has those 2 values.
The above can't have NULL values (enforced at UI level, avoids redundancy) nor duplicate rows (enforced by the primary key pktire_wear), so far so good?
HOWEVER, it does seem to have attributes which are duplicates in that you are conceptually talking about "tire_wear" in 4 fields. Now, I can see how you can end up with redundant code in queries and the resultant pain in having to adjust your application to deal with it if you are trying to future-proof your design to allow for cars which may have 100 wheels.
BUT..if you are strictly dealing with a situation where cars can only be 4-wheeled, is that a problem that doesn't apply here? ie: the app. is promoted as "Database for 4 wheeled car wear" - no we won't help you with tricycles, we're stricly a 4 wheeled car database provider
I would appreciate hearing from those in the know about the theory as well as real world application about this! I wonder if there's a balance between the two as stated above.
Thanks,
Kev
