Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the unit column necessary? From your explanation and data, it doesn't seem like it ever varies, or am I missing something? –  Petter Brodin Jan 4 '13 at 18:34
    
@petter-brodin Good question. Yes, it does vary depending on the compound. If I listed more compound types in my example, then you would see a few different types of units. –  qmoog Jan 4 '13 at 18:40
    
OK, what does it depend on? Is there a functional dependency from method to unit, meaning "if you know the method, do you know the unit?"? Why do you call it "units", BTW? Is it just a typo to use the plural form of the word, or do you intend to store multiple values in the column? If the latter, your schema isn't 1NF, as that requires atomic values. –  Petter Brodin Jan 4 '13 at 18:44
    
The compound determines the unit, actually. The compounds are heavy metals or other pollutants in this case, so compound 1 might be lead, which would be measured in grams/Liter. But compound 2 could be total suspended solids, which would be measured in milligrams/Liter. 'Units' is a typo. It would just be representing the single unit of the associated value. I've changed that in the question. –  qmoog Jan 4 '13 at 18:51

1 Answer 1

up vote 0 down vote accepted

Second normal form

If I understand you correctly, the combination of {date, compound_type, location, method} uniquely identifies {value, units}, and all four are needed in order to identify a unique sample ({date, location, method} isn't enough by itself, for example).

I'm going to write this as if I hadn't received an answer on my question about functional dependencies, since other people might be interested in an explanation of both possibilities.

If there are no partial dependencies

1) Assuming none of the non-prime attributes {value, unit} depend on part of the candidate keys {id} or {date, compound_type, location, method}, your table is in 2NF since, as Wikipedia puts it, "every non-prime attribute of the table is either dependent on the whole of a candidate key, or on another non-prime attribute."

If there are partial dependencies

2) One or both of the non-prime attributes {value, unit} depend on only parts of the candidate key {date, compound_type, location, method}. You've confirmed this is the case with {compund}->{unit}, so your table is not in 2NF.

In order to fix the violation of 2NF, I would suggest moving {unit} to the compound table, which I'm guessing would end up looking something like this: {id, name, unit}. Here, the candidate keys are {id} and {name}. Since there are no composite candidate keys, the table is automatically 2NF. It's also 3NF since there are no transitive dependencies, I.E. there's no attribute that's dependent on unit.

Third normal form

OK, that leaves us with the samples table looking like this: {id, date, compund_type, location, method, value}. The two candidate keys are {id} and {date, compund_type, location, method}, which leaves {value} as the single non-prime attribute. Assuming that there are no more 2NF violations (you can't use a subset of {date, compund_type, location, method} to uniquely determine value), we can check the table for violations against 3NF.

3NF states that every non-prime attribute (attributes that don't belong to a candidate key) must be directly dependent on every superkey. Since we only have one non-prime attribute, {value}, it's impossible for the table to violate 3NF, since there's no non-prime attribute for {value} to be dependent on, and no non-prime attribute that can depend on {value}.

I'm going to leave discussions about BCNF out of this for simplicity.

Surrogate key vs. natural key

As for your other questions: "is using an id(PK) column like I have above the best way to go with all of the repeating dates?" I think so. Semantically, the surrogate key id isn't necessary, but it does help keep things simple. I'm not sure how MySQL works underneath the hood, but in other DBMSs composite primary keys with non-integer data types can lead to unnecessary overhead for example when indexing. Another problem with composite keys is that it gets annoying to query them.

Imagine that you need to add information about which labs each sample was sent to. A sample can be sent to several labs and each lab can receive several samples, so you create a table to connect the two tables. Would you rather write this

SELECT *
FROM samples s
JOIN labs_samples ls ON 
    s.date = sl.date,
    s.compund_type = sl.compund_type,
    s.location = sl.location,
    s.method = sl.method

or this

SELECT *
FROM samples s
JOIN labs_samples ls ON s.id = ls.id

?

share|improve this answer
    
Thanks for the detailed answer. I had some problem visualizing the process of moving through NF, and your explanation helps me understand the process of thinking it through. You are correct in that no subset of {date,compound_type,location,method} can uniquely determine a value. Just to be sure I've got it straight in my head, I would end up with two tables, one with {id(PK),date(CK),compound_type(CK),location(CK),method(CK),value} and the other for compounds with {id(PK),name,unit}. Then the value of compound_type for each row would correspond to the id of the compound table? –  qmoog Jan 4 '13 at 19:40
    
If by (CK) you mean "Candidate key", that's not something you define in the database, it's just a theoretical concept which means the minimal amount of attributes that can be used to identify all other attributes in the relation. This means that they're not all candidate keys alone, but the combination {date, compund_type, location, method} is ONE candidate key, and {id} is the other. Your description of the compound table is correct. –  Petter Brodin Jan 4 '13 at 19:49
    
Perfect. You've answered above and beyond what I was expecting. Thank you very much! –  qmoog Jan 4 '13 at 19:50
    
Glad to help. I updated my reply with a direct answer to your question about why to use the surrogate key {id} rather than sticking with the larger candidate key, in case you didn't see it (I'm not sure if Stack Exchange notifies you when an answer is update). –  Petter Brodin Jan 4 '13 at 19:55
    
I see it now. It makes perfect sense to use the surrogate key, especially when I consider how I'm going to structure the statements for generating reports. –  qmoog Jan 4 '13 at 20:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.