Tell me more ×
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.
up vote 1 down vote favorite

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

share|improve this question
The L:M:N Hire_Equipement relation suggests that a Hire can be related to many Customers. If that is your requirement, I see no serious problem with the design. – ypercube Nov 4 '12 at 21:51
@ypercube No, that isn't my intention. How to fix it? by adding customer_id column to 'Hire' table? – Chiron Nov 4 '12 at 22:02
I think the same was suggested in your previous question. Yes: 1.Add customer_id to Hire 2.Drop the relationships between Hire_Equipement and the two tables: Hire and Customer 3.Make (customer_id, order_id) the primary key of Hire 4.Recreate the Hire_Equipement-Hire relationship targeting the new composite PK. 5.Add a new 1:N relationship between Customer and Hire (via customer_id). – ypercube Nov 4 '12 at 22:14
1  
Alternative: 1.Add customer_id to Hire 2.Drop only one relationship, between Hire_Equipement and Customer 3.Change the PK of Hire_Equipment to (equipment_id, order_id) and drop the customer_id from that table 4.Keep the Hire_Equipement-Hire relationship as it is. 5.Add a new 1:N relationship between Customer and Hire (via customer_id). – ypercube Nov 4 '12 at 22:18

1 Answer

up vote 1 down vote

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).

share|improve this answer

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.