Does the design adhere to 3NF

database-designnormalization

I created this schema with OpenOffice.

Does this design adhere to 3NF ?

(A relationship link between Equipment and Supplier_Equipment , Customer and Membership should be created but OpenOffice isn't allowing me to do so.)

enter image description here

Best Answer

Yes, this conforms to 3NF as far as I can see.

Basically 3NF is met when all non-key values (assuming natural keys here) in relations are non-transitively functionally dependent on the key. Surrogate keys don't count for this analysis since category.name is not functionally dependent on category.category_id. In fact, category.name is probably your natural key and category.category_id is functionally dependent on it for purposes of this analysis (because it is in essence a join dependency stand-in or surrogate for your natural key). Going through your tables here I do not spot any cases where this is violated.

The one obvious change that this suggests is what was mentioned in the comments by others, to move customer_id into hire and out of hire_equipment (if third party loans of equipment are supported you probably want customer_id in both places though).