Database design ideas

ihenman

Well I've got myself confused, I'm working on a site similar to www.shopping.com, it's a feedback and review site for just about everything you think of on the net.

No here's the problem I'm running into, I've broken down all the catefories, and departments etc. and figured out what tables I need.

I figure I'm going to need a Parent Category Table, then a Sub-category table, Sub-Category-Divisions table, then a Product type table.

Here's where I think I'm thinking too hard. There are all kinds of different products on this site, Computers, Clothes sporting goods etc. How would you reccommend I group the products together I'm trying to make this as easy as possible, but b/c there are so many differnt types of products I don't think I can void having several tables, more or less one for each product type really. But then it seems I could end up with like 1000's of tables?

What do you guys think? Each product is going to have to store a detailed description including specifications so it's kinda hard to figure out how I could store say a pair of roller blades and a Dell laptop in the same table, along with their specs.

drawmack

This is the table for categories, sub-categories, sub-sub-categories, etc. the parent field allows the table to be recursive so you can have as many, or as few, layers of sub catefories as needed and you can add levels without changing the database or the code.

categories
    id
    name
    parent
    <etc.>

the information that will apply to all products, regardless of type, is stored in this table.

products
    id
    name
    type
    <etc>

then you have a types table like this

types
    id
    name

then you have a types to attributes table like this

types_to_arrtibutes
    id
    type_id
    name
    datatype
    <etc>

then you have four tables to hold the attributes about the different attributes like this

int_attributes
    id
    prod_id
    tta_id
    attribute

float_attributes
    id
    prod_id
    tta_id
    attribute

varchar_attributes
    id
    prod_id
    tta_id
    attribute

blob_attributes
    id
    prod_id
    tta_id
    attribute

the tables may have a bit more information in them then I proviced, like the tta table should probably store information about requred, searchable and acceptable values too. However this is the basic skeleton of the database and with a database structured like this you will never have to change the code to account for new types or anything like that. The database is completely dynamic.

RadioGeek

I agree with drawmack.

When you are doing a recursive heirarchy, it's deceptively simple. For example, if you wanted to represent this tree:

...it's very easy. And, you only need one table. Consider that each category will have these properties: Category ID, Parent ID, Name. Ostensibly, each category will have its own Category ID. So, you would need to design a table as such:

TABLE: my_categories

CategoryID INT
ParentID INT

CategoryName TEXT

In response to your question about it getting complicated, consider this layout as it relates to the above example.

ITEMS: my_categories (CategoryID / ParentID / CategoryName):

1 / -1 / blah
2 / 1 / cat
3 / 1 / dog
4 / 1 / mouse
5 / -1 / another blah
6 / 5 / orange
7 / 5 / purple

In this example, I use negative one (-1) to signify that the category is at "the top" of the heirarchy. Since I never use negative one as an index in my tables, it works great. So take for example the "blah" thread. Its CategoryID is "1". Therefore, anything with a ParentID of "1" belongs UNDER "blah" since its CategoryID is "1".

Thus, "cat" (ID=2), "dog" (ID=3), and "mouse" (ID=4) belong under "blah" since their ParentID is "1" and "blah"'s CategoryID is "1".

Also, using this method makes searching and displaying the heirarchy on the screen much simpler.

Hope this helps.

Drakla

Hi Drawmack, Radiogeek - that solution is just so beautifully simple, I love it. I'm hopefully getting a job soon and that's also the perfect solution for what I need. I'd done notes for the database design I was going to do and lost them on a train - I'm pretty glad I did. It was pretty close to it but had an unecessary second table.

Is there a name for that sort of parent/child design?

drawmack

I've always called it 3gl database design. That comes from 3rd generation langauges (3gl) which are languages that change as you need them too, kind of like xml only different. Well this is a database design that changes as you need it to. I came up with it for a project I did for the military. They wanted an inventory system but couldn't tell me what they wanted to inventory cause that was classified.