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

  • One paragraph says: "a single MEMBERS can be associated to only one POS_STRUCT_DIV" Jan 11, 2013 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. Jan 11, 2013 at 20:07
  • Ah sorry, I described this in a wrong way. Edited for clarification.
    – errata
    Jan 11, 2013 at 20:15

1 Answer 1


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.

  • 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, 2013 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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