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
Relation goes like this:
POS can have many
POS_STRUCT but a single
POS_STRUCT can be associated to only one
POS_STRUCT can have many
POS_STRUCT_DIV but a single
POS_STRUCT_DIV can be associated to only one
POS_STRUCT_DIV can have many
MEMBERS but a single
MEMBERS can be associated to only one
POS_STRUCT_DIV will be consisted of few 'constants'. Let's call them
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_DIV? Also, since records in
MEMBERS will be sometimes associated to
POS_STRUCT_DIV with a same name but on different
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...: