CODEWITHSUNDEEP
mysqlsql
NiceToMytyuk
NiceToMytyuk

Reputation: 4327

Many-To-Many table or one table?

I hava a doubt about of which of the two approach will be more performable,

let's presume i have a database for each user, each user could have many shops so i have a table CONFIG with columns like SHOP_CONFIG (id of the shop) and POS_CONFIG (id of the pos) a shop could have more than one POS.

Then i have the table with ITEMS that belong to the shops which has columns like ID_ITEMS DESC_ITEMS, the items could be all the same for each POS in one SHOP or each POS could have different ITEMS. So now i have to see all the items for each SHOP / POS

So my doubt was:

Should i add an ID to CONFIG and then make a 3rd table like SHOP_ITEMS with ID of the CONFIG as foreign key and ID_ITEMS as foreign key?

Or could i just add columns SHOP and POS to ITEMS table?

(the table CONFIG is used anyway for other purpose)

Upvotes: 0

Views: 54

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

A bit longer example, but you can adjust it to your project.

-- Shop owner (user) OWN exists.
--
owner {OWN}
   PK {OWN}

-- Owner OWN owns shop number SHP# (of that owner).
--
shop {OWN, SHP#}
  PK {OWN, SHP#}

FK {OWN} REFERENCES owner {OWN}

-- Point of sale number POS#,
-- of shop number SHP#, of owner OWN exists.
--
point_of_sale {OWN, SHP#, POS#}
           PK {OWN, SHP#, POS#}

FK {OWN, SHP#} REFERENCES shop {OWN, SHP#}

-- Item ITM exists.
--
item {ITM}
  PK {ITM}

-- Item ITM is available in shop
-- number SHP#, of owner OWN.
--
item_shop {ITM, OWN, SHP#}
       PK {ITM, OWN, SHP#}

FK1 {ITM} REFERENCES item {ITM}

FK2 {OWN, SHP#} REFERENCES shop {OWN, SHP#}

-- On DTE (date-time), item ITM was sold in
-- quantity QTY, thru point of sale number POS#,
-- of shop number SHP#, of owner OWN.
--
sales {OWN, SHP#, POS#, DTE, ITM, QTY}
   PK {OWN, SHP#, POS#, DTE, ITM}

      FK1 {ITM, OWN, SHP#} REFERENCES
item_shop {ITM, OWN, SHP#}

          FK2 {OWN, SHP#, POS#} REFERENCES
point_of_sale {OWN, SHP#, POS#}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename SHP# to SHP_NO.

Upvotes: 1

Related Questions