I have this table
Storeswhich represents stores. Those stores are managed for many types of persons, like
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?
Up front disclaimer: I am a SQL DBA, so exact solutions may differ.
Two approaches that I have seen to this. The first is to ask if a single person can be an admin for more than one store. Can Paul Smith have store 27 and 19? or just one? If that is the case, then I would add the needed fields to that persons’ profile.
A second way of approaching it is to have a join table that would look like this:
Join_ID | Admin_ID | Store_ID | Admin_Type_ID
This way would be a many-to-many situation, where a single person can be assigned to multiple stores, while a single store can have multiple admins of the same or different types. You could also use this to have one person be a general manager at one store, but a network admin at another.
The overhead for this should not be too different than anything else going on in the database, and should only take a few extra minutes to plan out how the stored procedures would read and write to it.