mapping a single column in a table to two different columns in two different tables

Question

i have two tables Brand and Store

A brand can be sold in multiple stores and a store can sell multiple brands.

I have a bridge table brand_store.

There is a parent store table called StoreGroup.

This storegroup will contain all stores that belongs to a parent. For eg: All stores of KFC will be mapped to a single parent in the store_group table.

Now I have to create a User table where a user can play multiple roles.

if the user is an admin then he has to be tied to the storegrp if the user is a storeowner then he has to be tied to a particular store.

The merchant table looks like"

Merchant

id

login_email_id

login_password

store_or_store_group_id

role_id

....

Scenario:

Each KFC head can appoint an admin, a storemanager etc.

In the merchant table, if the role id is admin then it should be tied to KFC head in store_group table, but if the role id is store manager then it should be tied to a particular store of KFC in store table.

how do i handle this scenario. is this the right way of doing ?

Answer 1

If you are using InnoDB, and you want MySQL to enforce foreign key constraints, you'd need to create two separate columns in merchant table, rather than the single column.

 , store_id       int  comment 'fk ref Store'
 , storegroup_id  int  comment 'fk ref StoreGroup'

 , CONSTRAINT FK_merchant_store FOREIGN KEY (store_id) REFERENCES Store(id)
 , CONSTRAINT FK_merchant_storegroup FOREIGN KEY (storegroup_id) REFERENCES StoreGroup(id)

There's no declarative constraint to prevent both columns from being populated, of you want the database to enforce that, you'd need to create a BEFORE INSERT and BEFORE UPDATE trigger.

If you go with the single combined column, then you won't be able to define a foreign key constraint. You'll also need a way to determine whether the value stored in that column is a reference to the Store table or the StoreGroup table (or, is that the purpose of the role_id column?)

Answer 2

I think you're close. Your Store table can have foreign key "store_group_id" instead. And you just have the store id in your merchant table. Now you will be able to fetch the store_group_id based on the role_id (if he is 'admin' you can join the store table with merchant table to fetch the store_group_id). I hope this helps

Answer 3

I think, you should create different tables to bind merchants to different models. 'Merchant' is the common information about an user. And there are some (now only two) different roles. These roles define different behaviour. If now all differences in database lie in diverse bindings, then, later on, roles may acquire other different attributes. So, in my opinion there must be something like this:

Merchant table:
  id, login_email_id, login_password, role_id 


AdminMerchant table:
  merchant_id - FK to merchant table, Unique
  store_group_id - FK to Store group table

ManagerMerchant table:
  merchant_id - FK to merchant table, Unique
  store_id - FK to store table