Database tables with dynamic information

Question

I've googled this and found that it's almost impossible to create a database with dynamic collumns. I'll explain my problem first.

I am making a webshop for a customer. It has multiple computer products for sale. CPU's HDD's RAM ect. All these products have different properties, a CPU has an FSB, RAM has a CAS latency. But this is very inconvenient because my orders table needs foreign keys to different tables which is impossible.

An other option is to store all the product specific information in a varchar or blob field and let PHP figure it out. The problem with this solution is that the website needs a PC builder. A step-by-step guide to building your PC. So for instance if a customer decides he wants a new "i7 920" or whatever i want to be able to sellect all motherboards for socket 1366, which is impossible because all the data is stored in one field. I know it's possible to select all motherboards form the DB and let PHP figure out which ones are for socket 1366 but i was wondering,

is there a better solution?

Answer 1

look at making tables like:

Products
ProductID      int     PK auto number/identity
ProductName    string
ProductType    char(1) FK to ProductTypes.ProductType
ProductCost
ProductPrice
...

HDDs
ProductID    int FK to Products.ProductID
HDDCapacity
HDDSpeed
....

RAMs
ProductID    int FK to Products.ProductID
RAMCapacity
RAMCASlatency
...

ProductTypes
ProductType             char(1)  PK  "H"=HDD, "R"=RAM, etc.
ProductTypeDescription  string

you have one main table with all the common columns, and one-to-one tables for the specific columns based on the ProductType column. Your Orders table can now FK to Products.ProductID for all products.

Answer 2

What you're looking for is subclassing. One table will contain basic information relevant to all products. This is what you reference from the orders table. Then you can have other tables reference your products table and contain specific information based on the type of product. Something like:

               Orders
                  |
               Products
              /   |   \
           CPUs RAM Motherboards

Answer 3

For a dynamic database you could consider the EAV Database Model

Search SO for EAV will also yield a good number of Q/As that closely address your particular conundrum.

Answer 4

You might want to take a look at this question/answer in order to figure out if that's the way you really want to go... I'm not so sure.

The real issue is that you have highly related and very different components. However, they do share properties in common...

So, let's say you have a ProductProperties table that looks something like

ProductId 
PropertyTypeId
PropertyId
Value

The products table should be something like:

ProductId
ProductCategoryId
Name
etc...

Assuming product 1 is a processor with a 1366 pin out, and product 2 is a motherboard with a 1366 socket, then each would have a Property of the "Socket" type with a value of 1366. This would make it extremely easy to query for. By querying the ProductProperties table (joining on Products), just exclude the ProductCategory of the item they are currently looking at to find products in other categories that work... Or, if you know the category ahead of time (like motherboards) you just select that way.

You could go a little further on the admin side by defining Product Templates which automatically brought in the PropertyTypes at the time you are adding a new product. They would simply select the template, fill in the values and save..

As a side note, I'm glad to know someone is working on a cart like this. I love newegg, but one thing I've felt they've always missed on is being able to link things like which processors will work with which motherboards.