CODEWITHSUNDEEP
mysqldatabase-design
Cantinou
Cantinou

Reputation: 136

Different possibilities for a field

I have two tables:

Table Shop

idShop

shopName

idManager??



Table ShopManager

idManager

managerName

idShop??

I want to create a foreign key between these two tables. But I have a design issue:

Each Shop can have different ShopManagers and a ShopManager could work in every shop.

How can I solve it?

Upvotes: 0

Views: 31

Answers (2)

safin chacko
safin chacko

Reputation: 1390

You have two options.

  1. You can use idShop in ShopManager table and can store multiple shop id as a serialize format.
  2. You can use another table to store the relation between idShop and idManager. That table contain multiple entries(idShop) for each manager_id ie

id idManager idShop

1      5         6

2      5         2

3      5         9

Upvotes: 1

kondrak
kondrak

Reputation: 436

I'd consider creating an "assignment" table that consists of a ShopManager ID and a Shop ID. That way, multiple ShopManagers can be assigned to a single Shop, and multiple Shops can be assigned to a single ShopManager. The relationship between Shop and the assignment table would be one Shop to zero to many assignments, and the relationship between ShopManager and the assignment table would be one ShopManager to zero to many assignments.

Upvotes: 1

Related Questions