Design a table of items with many features; how to group those features

Question

I have this table Storeswhich 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?

Answer 1

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.