From your description, you'll need the following tables ("..." means you might need additional fields in that entity):
- User (UserID, EmailAddress, Password ...)
- Session (SessionID, CreatedDate, UserID, ExpiryDate, PurchaseCompleteInd ...)
- Product (ProductID, Name, UnitPrice ...)
- SessionProductLink (ProductID, SessionID, Quantity ...)
The SessionProductLink is a Junction table, which is the 'bit of the design' you were missing.
The field names in italics are foreign keys (it should be obvious which fields they link to ...). the ID fields are the clustered key fields in the first three entities, for SessionProductLink you'd want a compound key of (ProductID, SessionID). (You can use a surrogate key there if you want, that's a style decision you can make)
Other things you might want to think about:
- Security. You want to store your user passwords securely encrypted (i.e. use bcrypt), if you support credit cards you legally are not allowed to store the full credit card details in most cases, etc.
- Invoicing - which payment methods are you going to support? Do you want people to be able to pay for multiple 'sessions' with one invoice? (This is more common for business accounts, typically retail customers are one purchase = one invoice = one payment method) Do you want to accept foreign currencies? (Between this point and the first one, you'll almost need a payment processor to make sure you don't land yourself in massive strife ...)
- What happens if a product price changes? You don't want your past purchases/invoice prices to change, right?
- You'll need to store your sessions (or the completed ones at least) forever (depending on your country's legal requirements for document storage for commercial transactions), since they're a legal record. Similarly, you can't just delete old Product records, you need to mark them obsolete so they don't show up as available for purchasing but do show up in historical purchase records (otherwise your app will show for old items that someone purchased 5 of product ID 119 and 2 of product ID 135 and you don't know what the hell they actually bought...).
That's all I can think of right now, but that should be enough to get you started.
I recommend reading the following link on Database Inheritance: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx
Databases don't naturally fit hierarchies - they do work, but it's not their strong suit.
In your case I would change shops_and_items for
**products**(product_id (PK), shop_ID (FK), ... then columns for common fields: name, price, stock, delivery time)
Then...
Option 1 :: Fewer Product Categories
If you only intend to have furniture and clothing then you could do
**clothes**(product_ID(FK and PK), size, colour, gender etc)
**furniture**(product_ID(FK and PK), height, width, depth, material etc)
Note that product_ID is a foreign key and primary key... a product would either have a corresponding record in clothes OR furniture. This doesn't need to be auto incrementing. A product has one clothes item or one furniture item (not both).
Option 2 :: Many Product Categories
If you intend to expand to lots of different products (electrical, food, ) and possibly sub-products then you will have a lot of tables and it will be a mess to manage! In this case it's easier to go down a meta-data route like WordPress http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png
WordPress uses wp_posts for a post, page, any many other plugin-defined types (map, questionnaire, gallery etc), if they need extra properties then they are stored in wp_postmeta.
You would keep the products table but add on:
**product_meta**(product_id(FK), meta_name, meta_value)
e.g.
4 (Surfer T-Shirt), colour, white
4, size, Medium
4, gender, male
18 (TV Stand), material, metal
18, height 40cm
18, depth 33cm
18, width 60cm
This might need some more validation before the data goes into the database as meta_value will most likely be a varchar column so you will need to standardise how you interpret values. Will your code use M/F for Male/Female? What units for measurments?
I recommend grabbing some open source shopping carts and installing them just to find out how they structure their SQL code - they will have their own solutions for sub-categories and custom properties of products.
Best Answer
I would solve this by adding a generic PRODUCT table containing the attributes which are common for all products (including the ProductID) and a ProductType column describing the type of product (Car, Book etc). Tables CAR, BOOK can then contain the same ProductID as in the main PRODUCT table and attributes which are specific to their product type.