What is the best way to represent data that refer to each other in sql?

On The Net Again 0
Viewed 27

So I am making a website where I have 2 types of products that reference each other.

This is easier to show with an example.

Apples sold in StoreA, StoreB, StoreD, StoreF
Oranges sold in StoreA, StoreC, StoreD, StoreE, StoreF
Lemons Sold in StoreA, StoreD
Grapes sold in StoreD, StoreE, StoreF
Peaches sold in StoreB, StoreC, StoreF
Limes sold in StoreC, StoreE, StoreF

StoreA sells Apples, Oranges, Lemons
StoreB sells Apples, Peaches
StoreC sells Oranges, Peaches, Limes
StoreD sells Apples, Oranges, Lemons, Grapes
StoreE sells Oranges, Grapes, Limes
StoreF sells Apples, Oranges, Grapes, Peaches, Limes

What is the best way to represent this in sql tables?

relationship tsql sql
1 Answers

I'd probably create three tables, one for stores and one for fruits, both with and ID column that's a primary key. Create a third table also with an ID column which is a primary key and with two other columns FruitId and StoreId which have a foreign key relationship to those tables. This way you can't add a fruit or a store to the third table if they don't exist.

MatFitz0
Related