Setting up simple relational database structure

Question

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

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.