I have this table Stores
which represents stores. Those stores are managed for many types of persons, like Administrators
, Managers
, Supervisors
and so on, i.e. Each store has an administrator, a manager, a supervisor
I could make N tables for each type/position and link them by their respective id to the Stores
table, but the requirement says it is possible that the positions could increase.
With that being said, is the following approach correct? Create a Stores_structure
table with a foreing key 'id_position_fk' linked to a Positions
table. That Positions
table has listed:
id_position | description
_______________________________
1 | Administrator
2 | Manager
and so On. And the Stores_structure
(still looking for a better table name) has:
id | id_position_fk | Name | phone_number
_____________________________________________________
1 | 1 | John Smith |
2 | 1 | Paul Smith |
3 | 2 | Robert Smith |
And the id from Stores_structure
is linked in the Stores
table with a many to many relationship
The first red flag I see is with that approach: it is posible to have two 'Administrators' for a single store; other thing I'm concerned about is the 'excessive' "normalization" of the many-to-many table
Is there a better approach?