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.

Using MySQL

I need help determining how to model a Keywords table to three different tables (Products, Certifications, and Exams) - I have included my model which is using what I refer to as intermediary tables (what would they be called), but im not sure that is correct. Can someone tell me if this is correct, or show me an example of how to do it correctly? Thank You!

Data model

share|improve this question

2 Answers 2

Your design is correct. Tables with many to many relationship needs to have an interface table. Interfaces table contains foreign keys to the main table, it must have a primary key. Good luck

share|improve this answer
    
Can you explain what you mean with "it must have an ID field"? What is "it"? –  ypercube May 10 '13 at 23:47
    
hello, yeah, Answer edited. I should have said the primary key.... thanks –  Masoud Afrashteh May 11 '13 at 6:57

You need three more constraints on each of your linkage tables. The first constraint says the the two FKs, taken together, are unique. The other two constraints say that neither of the FKs can be NULL.

If your linkage tables had declared a composite PK consisting of the two FKs, you would have gotten the same effect with just one constraint. The Id on the linkage tables would then be unnecessary. The consequences of a composite PK on performance is another question.

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.