I am attempting to get a MySQL database to 3NF, but I'm unsure as to how to get there. I'm new to database design, so please correct me if my understanding is incorrect.
I have 6 columns:
date(yyyy-mm-dd), compound_type, location, method, value, unit
The database represents values of samples taken on a particular day at a particular location. There are 21 locations that happen for every date event that samples are taken, and 10 different types of compounds that are tested at each location. There are three different methods to run on each compound. So there is a value (and associated unit column) for each method for each compound type at each location on each day.
I got a pseudo-1NF by setting up a table like so:
id(PK), date, compound_type, location, method, value, unit 1, 2011-07-03, 1, 1, 1, foo, g 2, 2011-07-03, 2, 1, 1, bar, g 3, 2011-07-03, 1, 2, 1, 789, g 4, 2011-07-03, 2, 2, 1, 123, g 5, 2011-07-03, 3, 2, 1, 345, g
And et cetera for all other compounds and locations and methods. Obviously, there are a lot of repeats of dates for every compound, location and method combination. To me, it seems fairly redundant to have that much repetition.
My question is: is it possible to further normalize this database, or is using an
id(PK) column like I have above the best way to go with all of the repeating dates? Am I better off using indexes or unique keys? Would it be better to have a table for every date which breaks down into the compound/location/method categories?
I'm a newbie, so any resources you can point me to would be excellent.
Eventually, I'm going to be writing a front-end to this database for reporting and graphing, so I'm looking for a database design that will allow me to use brief SQL statements for returning data.