A Simple catalog: A Minimal Data Model

Giving the categories a unique ID, parent ID and display order allows for the building of an ordered n-level category tree. The only real weakness of this that I've found is that no category can have two parents, but I accept that limitation rather than succumb to the complexity of adding a separate category to parent category joining table. For display purposes I find it is often useful to expose a calculated nest level attribute as well.

I use a title for the human readable identifier for the category and calculate a "name" attribute (the title, but all lower case, remove special characters and replace spaces with hyphens) for concatenating search engine optimized URLs if required). Categories may also have short description (summary), description (typically a text field with a WYSIWYG editor), thumbnail image, main image, and/or additional metadata fields. I typically also include a "template" property if different categories will use different views and a "type" property so admin users can easily make a given category the "latest products" or "most popular" or other special category just by selecting from a predefined list.

For products, I use a product ID, display order, title and (calculated) name as for categories. The decision tree rules for generating the product attributes can get fairly complex, but I usually offer optional SKU, short description, description, thumbnail image, main image and supersize image together with the ability to add unlimited project specific attributes like vendor or beads per inch or whatever. In addition, purchasable products need a price (I usually provide cost, price, sale price and MSRP unless the project has quantity or customer specific pricing in which case I use a separate pricing table) and anything that is shippable via courier may need a weight, dimensional weight and/or a "ground only" bit. Shippable products can also be associated to shipping regions if you have products that can't be sent internationally and right now I typically have a "product taxable" bit although eventually I'll probably have to upgrade to a "product to tax rate in jurisdiction" joining table if my clients ever care to capture the fact that the same product may be taxable in one state but not in another.

I typically find my clients need the ability to associate each product to multiple categories and some want the ability to add a display order to the joining table (as products may be manually ordered differently within each category) although as a practical matter I find that a separate "display order" admin screen is required within the category to make that usable. That means a ProducttoCategory table with ProductID, CategoryID and DisplayOrder as a minimal field set.

There are all kind of cool features I've dropped in before such as vendor specific drop shipping, but I think the summary spec above will do for me to implement this simple catalog and its associated cart and checkout before Monday.

Oh, yes, I know that most catalogs also support attributes and attribute options (size=small, color=red, etc.) and some support "master products" for implementing one SKU per attribute combination (if you have red, small shirts and green medium shirts but no red medium shirts and need to capture that distinction), but I'll add that functionality back into the system when I have a project that needs it along with vendor specific drop shipping and any other cool features I've forgotten. As for cross sells, I'll cover those in a seperate post.