Database tables with dynamic information

Question

I've googled this and found that it's almost impossible to create a database with dynamic columns. 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 etc. 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 select 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 from 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.