CODEWITHSUNDEEP
phpmysqlsqldatabase-design
MAX POWER
MAX POWER

Reputation: 5438

MySQL Database Structure

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.

Upvotes: 0

Views: 752

Answers (5)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

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).

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

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.

Upvotes: 1

user895553
user895553

Reputation: 1

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

Upvotes: 0

cdhowie
cdhowie

Reputation: 168958

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.

Upvotes: 0

Related Questions