Database design - relation between 3 tables

Question

I'm new in relation Database and I try to a design relation between tables but unable to figure out something.
I'm trying to design a supermarkets application which should save information about every supermarket in my system (I could have more than one super market from some type).

I have these relations in mind:

but I need help to find the relation between the supermarkets and prices or how to get information about price about some product in specific supermarket.

Answer 1

Your design should be in a below way

super_market table --------> Product table

Product table --------> Price table

Explinations,

1. Supermarket is the primary table which will have highlevel information such as
   address, place, reputation .etc,
2. Product table should have list of products referensing to supermarket table
3. Price table should have detailes informations of each product and its price, varity, quantity, brand ,etc,.

Super_market
Product(reference primary key of Super_market)
Price(reference primary key of Product

Go through below link and understand the right approach for your design. https://www.computerweekly.com/tip/Inmon-or-Kimball-Which-approach-is-suitable-for-your-data-warehouse

Answer 2

You should make at least 4 tables:

1. ProductOutlet: ProductOutletID (primary key), ProductID, OutletID
2. Prices: ProductOutletID, Date, Price
3. Products: ProductID (primary key), ProductName
4. Outlets: OutletID (primary key), OutletName, Location

This is the right way to normalize tables (despite we have table Prices with composite primary key including 2 columns). So, the relations will be:

• 1 - ProductOutletID - 2
• 1 - ProductID - 3
• 1 - OutletID - 4

Answer 3

Supermarket - supermarket(primary key), Location

Product - product_id(primary key), description

Price - supermarket(primary key), product_id(primary key), price

Instead of the relations shown in the your diagram, the relations have to be,

• Supermarket and Price
• Product and Price