Forum Moderators: open
I am creating a website to sell products, some of which will be clothing. I would like to have the options for color and size in a drop down list.
I've got most of this figured out now but my problem is how do i keep track of inventory.
Here is some of the db structure I have established:
products (table)
- productID
- productName
- optionsID
- price
- image
- description
- inventory
options (table)
- optionsID
- productID
- size
- color
- inventory
Any suggestions for altering the db structure to handle inventory (for each option) would be greatly appreciated.
My goal is to have the website update the inventory after each sale (-1) if the inventory gets down to zero on an option, that option will not show up. If all the options have an inventory of 0 then the product will not show up. I will figure out the php for all of this later, for now I just want to make sure I have a db structure that works.
Not only that, you will have to move purchased items into "pending" until they are shipped out rather than just decrementing "in stock". Once you begin experimenting with this, you will see why this is necessary . . . more information here in a previous discussion [webmasterworld.com].
This . . .
options (table)
- optionsID
- productID
- size
- color
- inventory
is a bad idea. Why? What happens when you have a new option, like, for example, number of stones set in a ring, or carats of a diamond, shoestring colors, or something else? The first mistake you can make is thinking you "know" that you will always be set in "these" parameters (color and size.) When those parameters change, you have to recode the whole thing or add columns to your database.
You want a structure that will allow you UNLIMITED options. The options should be able to be added to any product at any time. Every product can have as many options as necessary. Most importantly, the price of the option should override the base product price if it needs to (small, medium, or large) or ADD to the base price if it's an add-on (as in a cover for your new widget.)
I have something like this:
products_tbl
-id
-product_id
-category
-category2 (THIS is a perfect example of a BAD CHOICE on my part!)
-ptitle
-short_description
-code
-price
-retail_price
-sale_price
-width (the following are for shipping)
-height
-length
-weight
-description
-active (display or not display?)
-free_ship
-sold (stores TOTAL purchases, all options)
-pending (stores total items pending)
-stock (shows total items in stock, all options)
-created_dt
-last_modified
The options table is almost a duplicate, allowing the programming to decide what price to display/calculate, or add to the base product price if necessary:
poptions_tbl
-id-sequence (order to display in!)
-option_name (color, size, trim, .....)
-option_value (red, blus, small, med., large...)
-product_id (join on products)
-code (if diff, which it should be)
-price
-retail
-sale_price
-width
-height
-length
-weight
-sold
-pending
-stock
-last_modified
-created
-active
-required
required is an important field that serves multiple functions. If it's required, it's not going to be an "add-on" (size, color, etc.) So you use this field to trigger any not-selected options in Javascript, and capture them server side. Also if the price is not '0.00' in this option, it means to override the base price if it's required, or add to it if it's not required (if it's a large, use this price; if it's a cover, add to product base price.)
The same is true of dimensions and weight: if this options overrides the base product, allow the dimensions to override as well, and send this to the shipping API for proper shipping calculation.
You might say "well I might have color and Color in there, isn't that going to bloat the database?" You control this with your programming. If you have an option for "Color" for a given product, you don't allow someone to add "color."
Now for the inventory:
product_inventory
-product_id
-quantity
-options
-last_modified
Highly inefficient, but I'm storing options as a comma separated list in the options field, so for any given product/option combination, I have a set pattern of option id's. So for colored,large widgets, I would have as many rows as required; that is, if I have red, green, blue widgets that come in large and small, I may (or may not) have rows representing each (they are created or updated as required.) The actual inventory for this product, this option combination, is stored in quantity.
Stock and pending are the same:
product_stock
-id
-quantity
-product_id
-options
-last_modified
product_pending
-id
-quantity
-product_id
-options
-last_modified
Now it gets sticky, where we store all this in a purchase. I'll be brief, as this post is already long, but suffice to say you have a purchases table, a purchase items table, and joining to the purchase items table you have a purchase options table which stores the actual selected options. All of these tables interact in th programming to do the most important thing, give the customer a clear view of what's in stock and what's not for any given item and it's related options.
When a purchase is made, items are moved out of stock and into pending. When the cart displays, we count stock minus pending.. We don't move items until the purchase is finalized (competitors can "cart up" all your stuff making it look like you have no stock.)
When the item is shipped, it's moved out of pending and stock is decremented.
It's not a perfect system, but it's expandable and I don't have to re-code it on a regular basis.
