CODEWITHSUNDEEP
database-design
Candy Chiu
Candy Chiu

Reputation: 6679

Store data collected from multiple source, and desinate a set as official

I want to design a table called Fruit which stores the Name, Price, Source of a type of fruit. Sample data: 

(Apple, 100c, SuperMarket1)
(Orange, 101c, SuperMarket1)
(Apple, 99c, SuperMarket2)
(Orange, 103c, SuperMarket2)

I designate the Orange price from SuperMarket1 as official, and the Apple price from SuperMarket2 as the official.

Currently, Source is not the primary key of the table, therefore, all non official entries do not show up. If I want to store the non official prices, I need to turn Source into part of the primary key. To designate an entry as offical, I then need to either enter extra entries using Source=Official, or create a column designated as official?

My goals are:

  1. have the ability to store all available pricings, not necessary in one table.
  2. the source of the official entries should be easily identifiable.
  3. ability to query for the official in minimum number of joins.
  4. there can't be more than one official prices for a type of fruit.
  5. the set of fruits is not small (fruit is just an example) and evolving. maintaining an extra table to store a official Source for each type may be a huge burden down the road.

Any idea to a clean design?

Upvotes: 0

Views: 58

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Looks like you'll need something similar to:

enter image description here

NOTE: The FRUIT.STORE_ID is NULL-able in the model above, to break the FK cycle and allow new data to actually be inserted at run-time. If your DBMS supports deferred constraints, this field can be made NOT NULL (unless you actually want to allow a fruit without official price).

1) have the ability to store all available pricings, not necessary in one table.

In this model, prices are not per fruit, but per fruit per store.

2) the source of the official entries should be easily identifiable.

This is what FRUIT.STORE_ID is for.

3) ability to query for the official in minimum number of joins.

SELECT * FROM FRUIT JOIN PRICE
    ON FRUIT.FRUIT_ID = PRICE.FRUIT_ID AND FRUIT.STORE_ID = PRICE.STORE_ID

4) there can't be more than one official prices for a type of fruit.

Each fruit is represented by one row in FRUIT and FRUIT.STORE_ID cannot store more than one value per row.

5) the set of fruits is not small (fruit is just an example) and evolving. maintaining an extra table to store a official Source for each type may be a huge burden down the road.

Fortunately, databases are very good at maintaining copious amounts of data.

Upvotes: 1

Related Questions