0

I am developing a stock ordering system in PHP/MySQL. Products can have multiple suppliers. Products are added to the basket by specifying the quantity required and the supplier. This is the current DB Structure:

Products
=========
id (PK)
name
etc...

Suppliers
==========
id (PK)
name
etc...

Product_Suppliers
=================
id (PK)
product_id (PK)
supplier_id (PK)
price

Basket
=======
product_id (PK)
quantity
product_suppliers_id

The Product_Suppliers table stores the different suppliers and their prices for each product.

When adding a product to the basket, it first checks to see if the product_id already exists in the Basket table - if it does then it will simply overwrite the record. If it does not exist then it will create a new record.

The product_suppliers_id field in the Basket table specifies which supplier has been selected for that particular product.

I wanted to know whether I have got the correct approach here, or whether I need to make any changes to eliminate any redundancy.

Improve this question

5 Answers 5

Reset to default
1

In Basket, you should have:

basket_id (PK)
product_id
supplier_id
quantity
price

Product_Suppliers is merely a configuration table. It has the only the current configuration of a product offered by a Supplier. Your Basket should 'know better' then pointing to Product_Suppliers because:

  • Products can change price.
  • Products can also cease to be offered by a Supplier at a given point.

Your design should allow you to query for historical data correctly, even if a product changes the price or if it's not offered by the supplier anymore.

Thus, you should not have product_suppliers_id in it.

Also, I don't see in your design whose basket that is, meaning you should also have a table for Clients or Users (or both, if users can input their own orders AND the store internal users can input orders for the clients) and the corresponding key(s) (client_id, user_id).

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi Adrian, thanks for your answer. Can I just also add that Basket is only a "temporary" date store - i.e. products get added to the Basket table and then it gets cleared out once it has been printed off. The table data is not then stored permanently anywhere. Do I still need to make your changes in light of this?
In that case, Basket should only have quantity and product_suppliers_id. From product_suppliers_id you can have product_id and supplier_id.
So I should take out product_id and use product_suppliers_id instead? But then that could cause a problem, as you have stated: "Products can also cease to be offered by a Supplier at a given point."
Well, you said the Basket is temporary, didn't you?
1

product_id seems to be redundant in Basket table. Also, I'd probably create a table that keeps track of different prices (surely, if prices change over the time). So I'd have:

  1. Product_Suppliers (id (PK), product_id (FK to Products), supplier_id (FK to suppliers), active(flag), UNIQUE constraint on (product_id + supplier_id))

  2. Product_Suppliers_Versions (id PK, product_suppliers_id(FK to Product_Suppliers), price, effective_date)

  3. Basket (id PK, prodcut_supplier_version_id(FK to Product_Suppliers_Versions), quantity)

  4. AFTER UPDATE, INSERT TRIGGER on Product_Suppliers that copies previous version to Product_Suppliers_Versions) and prevents modification of supplier_id and product_id if there was at least one purchase tied to it.

Improve this answer

2 Comments

But removing product_id from Basket would mean that I can't check if that product is in the basket or not. The Product_Suppliers table would keep track of prices.
Product_Suppliers has product_id, so you can get this info from it.
1

This is far from final, but just to point in a certain direction.

A Basket may have many items, so BasketItem is introduced.

BasketItemNo is ordinal number (1,2,3 ..) for each BasketID.

ItemPrice is copied to BasketItem.UnitPrice in order to allow for future price changes. Any price change should not change historic basket data.

If ProductDescription may change too, then that also should be copied at a time of purchase.

enter image description here

Improve this answer

1 Comment

Every user can have one basket at the time. when basket ready to submit as a order, register it in order table and that order table have order items that include product id's.
0

Technically, Basket can omit product_id since this is available through a join against Product_Suppliers on product_suppliers_id. This might complicate your SQL a bit, so it may be worth keeping the value closer to where you need it.

Improve this answer

Comments

0

You may want to assign to Basket its own PK (in case you ever want a basket with several products, or the same product and different providers). You could assign a unique index to (basket_id,product_id) if you want to force one-to-one relation between products and baskets at the db level

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.