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'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain...

I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS.

Relation goes like this:
- POS can have many POS_STRUCT but a single POS_STRUCT can be associated to only one POS
- POS_STRUCT can have many POS_STRUCT_DIV but a single POS_STRUCT_DIV can be associated to only one POS_STRUCT
- POS_STRUCT_DIV can have many MEMBERS but a single MEMBERS can be associated to only one POS_STRUCT_DIV

My table POS_STRUCT_DIV will be consisted of few 'constants'. Let's call them div_1, div_2 (...) div_n, and this is where my confusion starts.
Since this table will probably never change names or amount of those 'constants' should I even create a relation between POS_STRUCT and POS_STRUCT_DIV? Also, since records in MEMBERS will be sometimes associated to POS_STRUCT_DIV with a same name but on different POS and POS_STRUCT, should I create foreign keys for all 3 tables in MEMBERS table? What would be the most efficient way to solve this relation?

I tried to create an UML for this db, although I'm obviously not sure if it is correct at all...:

UML diagram

share|improve this question
    
One paragraph says: "a single MEMBERS can be associated to only one POS_STRUCT_DIV" –  ypercube Jan 11 '13 at 20:06
    
And the other "since many MEMBERS will be associated to POS_STRUCT_DIV with a same name but on different POS and POS_STRUCT" This seems like a contradiction. –  ypercube Jan 11 '13 at 20:07
    
Ah sorry, I described this in a wrong way. Edited for clarification. –  errata Jan 11 '13 at 20:15
add comment

1 Answer

up vote 1 down vote accepted

Unless you embed a list of pos_div_name in a column with type TEXT, you need to create table POS_STRUCT_DIV (POS_div in your diagram) as it has one-to-many relationship with POS_STRUCT.

You could use pos_div_name as the foreign key in the MEMBER as the name is never change. It saves you from looking up pos_div_name when loading MEMBER but the row size could be larger. So it is up to you.

share|improve this answer
    
OK, thanks for helping, I think I will set it up this way and just test the functionality in real world environment :) Since I will use Django, I think I will have all related data on disposition anyway. –  errata Jan 15 '13 at 6:59
add comment

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.