Hello!

I know there are some people who like tackling programming and database problems. So here's one that's fun for all the family!

The Problem
Design a database for a category system on an e-commerce site that allows each product to be assigned a core "type" such as Book or DVD which dictates the properties the product has and a set of categories or tags which can be used to populate these properties. Some properties might be allowed to have multiple values. If two categories have the same name but are used in different places they should be treated as the same when filtering by that category.

Types are distinct from categories in that they have hierarchy whilst categories don't and they dictate range of properties whilst categories populate them.

Should be designed to allow it to be easy to add new things and for fairly straightforward queries to get lists of products based on certain properties.

I have a vague idea of how I'd solve this, but I'm looking for other opinions. It's a suprisingly complex problem and I'm hoping someone can figure out how to make it into a simple one.

How many different 'types' of items are we talking? Is it just books/dvd & a handful of others? Or is it a larger scope?

I have a few different ideas will write them out and see how many pan out properly before I go posting them lol

let's say about 8 types, kind of like the different top-level sections on amazon, but each one might have several sub-types that are slight variations. The differences for subtypes will mostly just be the name though. So a comic is a subtype of book but you can probably assume that no additional properties are added.

So, types dictate the properties an object can have, whereas categories provide enumerated options of what values those properties can have?

Do you know ahead of time what these properties are?

Just a small and obvious idea:

All types share certain categories like price and availability, so this set of base categories should be generated automatically when a new type is created. That way you can focus on unique categories for each type.

I forgot to mention that Freelancer, I assumed it was implicit. Properties that are common to all products don't need to be worried about, they'll just be fields in the product table.

Some properties might be non-category too, like a publication date or a number. I'm not sure if that changes peoples approaches.

Wolfy, you can know what the properties are enough in advance that you can change the database to accomodate new ones by adding a new field. You shouldn't need to make changes to the relations to add new properties though.

An obvious thing would be to put every single property in the product table and just use the types to work out which are "active" for each product. But this approach means you lose any database-level validation on required fields because you'll have to make almost all of them not required.

My starting point would be having a table of the category names, ids and url slugs (for use in friendly urls) then have a many-to-many relationship between category names and properties. So you could make "Kevin Smith" all three of "Director", "Actor" and "Author" without having to define him again for each usage.

Wait, I'm confused. Types dictate ranges (of properties). Categories populate ranges (of properties).

So, um, why can't types just have properties? Or categories (books, DVDs) should contain types (non-fiction, thriller) which in turn contain properties (price, length).

Non-fiction and thriller would be categories for the genre property that can be considered to apply to all products.

Type = What kind of physical item is it? A non-fiction book and thriller have the same basic physical attributes so genres aren't Types

Property = An attribute or characteristic of the product, such as authors, dimension, whether batteries are included or not.

Category = Essentially just a piece of text that is a member of an enumerated set of possible values for a property. But the same category can belong to many of these enumarations

(The naming convention could be improved, but it's probably too late to fix that now).

Why not have a table that will hold the common properties of all objects, and then have individual tables for more specialized attributes? Then, in the aforementioned "common properties" table, have an ID foreign key referencing the primary key of a unique item table, as well as a column that states what table the item's more specific properties are stored on?

It would make SQL joins pretty much impossible (unless you selected the table name before hand and then built a query string using what was returned), but it'd cut down on clutter.