Shopping Cart Database Diagram

[foodpk]

Yo. I'm developing a shopping cart system for a company and I was thinking if you people could take a glance at it, tell me what you think, what could be improved, ekcetera (I'm looking at you, bwh2, ha!).
Anyway, the ERD is attached. I didn't bother with a lot of attributes and partiality in the diagram, this diagram is more about establishing semantic and good relationships between the entities, I'll add the attributes later.

As you can see, one item has many tags. An item belongs in several orders and one order has several items in it, the item2order table also has a quantity attribute, which tells you how many of something someone has put on the order. An item belongs in a category, each category belongs to a parent category. One account makes several orders and has several shipping addresses, it's noted in the order which account made the order and which shipping address that should be sent to.

You think I should add anything else? I was thinking maybe adding a step and order2step table, so the owner of the store could add several steps (like order handled, item packaged, item shipped out, transaction closed etc.) and then an order would have one or more of these steps. Do you think that's overcomplicating things?

[bwh2]

to start, i would do these things:

rename Tag to ItemTag. create Tag table with TagID, TagName.
rename Order_id to OrderID
remove the second CategoryID in Category.
create a CategoryRelationship table with ParentCategoryID and ChildCategoryID. both FK to CategoryID in Category
remove CategoryID from Item.
create an ItemCategory table with ItemID and CategoryID
rename Item2Order to ItemOrder. mere semantics though.
remove Quantity from ItemOrder. Quantity is always implied as 1. you can do GROUP BY queries if you want to present this differently. but internally, it should be stored with Quantity implied as 1.
add ItemPriceID to ItemOrder because prices can change. if a user checks his account history, you need to have the historical price saved and showing, not the current price.
^ as such, create an ItemPrice table with ItemPriceID, ItemID, DateActive, Price. that would be the price per 1 unit. this allows for historical tracking of your prices. when presenting the current price, grab the one with MAX( DateActive )
rename ShippingAddress to Address because we need billing addresses also. likewise, change ShippingAddressID to AddressID
in Order, make these cols: ShippingAddressID, BillingAddressID. both FK to AddressID in Address. both required fields.
add an AccountAddress table with AccountID and AddressID. currently, to get an Address associated with an account, there must be an order placed. that shouldn't be. so you need an AccountAddress table to access that data.
add an isDeleted column to Address. this would enable a user to remove an address, but still have that address associated with the order for historical tracking purposes.
^ similarly, add an isDeleted column to Item. this allows for historical tracking of Items, but enables you to prevent presentation on the catalog pages.
i would add an OrderStatus table with these columns: OrderID, StatusID, DateChange, Notes. StatusID would link to a Status table with StatusID and StatusName. StatusName examples would be: ordered, packaged, shipped, closed, etc. just as you mentioned.

i'll let you know if anything else jumps out at me.

[foodpk]

Hey, thanks man, that makes a lot of sense, I'll implement most of those things. Why would you make a classic M:N relationship between Item and Tag? In my case, the tag itself is the ID and if I want to display all the tags, I'd select distinct. I thought this decreases complexity, you know.

In the changes you were proposing to the categories, it seems as though you thought to make it so that an item can belong to many categories and that a category can also be a child of many categories, right?

If I removed the quantity from the ItemOrder table, I'd have to add a normal PK to it, right? So it would have a PK and two PFKs, because otherwise adding two items of the same type one order would result in primary key duplication.

How would you propose I go about creating discounts and also adding the feature of historical tracking of discounts, like with the prices?

[bwh2]

Why would you make a classic M:N relationship between Item and Tag? In my case, the tag itself is the ID and if I want to display all the tags, I'd select distinct. I thought this decreases complexity, you know. using DISTINCT works but it really isn't the answer. to use DISTINCT you first need to pull back all of the rows. so you're probably pulling back tons of extra rows, then using DISTINCT to weed out the duplicates. what if you want to change how a tag is spelled, capitalized, etc? in your way, you need to run an UPDATE across probably more than one row. that's not as clean or easy as just updating a standard Tag table.
i'm looking forward to a possible system where tags can be related to each other. so you make a TagTag table. it's a lot easier if you're just passing TagIDs than the actual tags. also imagine updating a tag name when you add more tables like this.
in your current system, a tag can't exist unless it has at least one Item associated with it. if you want to present it like that in the interface, that's fine. but it shouldn't be stored like that in your backend.
doing it your way might decrease complexity up front, but you could be shooting yourself in the foot down the line by including redundant data.



In the changes you were proposing to the categories, it seems as though you thought to make it so that an item can belong to many categories and that a category can also be a child of many categories, right?yes to both of those. although you could limit this by making the ChildCategoryID UNIQUE in CategoryRelationships and ItemID unique in ItemCategory.

If I removed the quantity from the ItemOrder table, I'd have to add a normal PK to it, right? So it would have a PK and two PFKs, because otherwise adding two items of the same type one order would result in primary key duplication.yes, i would add an ItemOrderID as well.


How would you propose I go about creating discounts and also adding the feature of historical tracking of discounts, like with the prices?yeah, the pricing thing is the trickiest part of the puzzle. i would probably do something like:


ItemPrice
--------------------
ItemPriceID (PK)
ItemID (FK)
Price
DateStart
DateEnd

make DateEnd default to NULL. DateStart default to the current GETDATE. only special discounts would have a DateEnd. thus, you could check for a current discount and get the current price:


/* Get ItemPrice info including any DiscountPrice and the end date for that discount */
/* untested... */
SELECT
a.*,
b.DiscountPrice,
b.DiscountPriceDateEnd
FROM ItemPrice a
INNER JOIN (
SELECT
ItemID,
Price as DiscountPrice,
DateEnd as DiscountPriceDateEnd
FROM ItemPrice
WHERE ItemID = 5
AND NOW() BETWEEN DateStart AND DateEnd
LIMIT 1
) b
on a.ItemID = b.ItemID
where a.ItemID = 5

[foodpk]

Har! Thanks man! Much appreciated. I'd better get back to making this ERD with full attributes and such. How much do I owe you for consulting?
One more thing, does anyone have any experience with these shopping carts about items. Do shopping cart systems usually keep track of how many items are in stock of each product and then maybe substract from that every time that item is sold or do they normally just have an isInStock field or something and then the manager of the store alters it manually in the system when they run out of stock for an item.