Dynamic Information Database Tables

Question

I searched for this and found that creating a database with dynamic columns is almost impossible. First I will explain my problem:

I am doing an online store for a customer. It has several computer products for sale. CPU HDD RAM, etc. All these products have different properties, the processor has FSB, RAM has a CAS delay. But this is very inconvenient, because my table ordersrequires foreign keys for different tables, which is impossible.

Another option is to store all the information about a particular product in a varchar or blob field and let PHP understand this. The problem with this solution is that the website needs a PC builder. Walkthrough on creating a PC. For example, if a client decides that he needs a new “i7 920” or something that I want to select all motherboards for socket 1366, which is impossible, because all the data is stored in one field. I know that you can select all the motherboards from the database and let PHP figure out which ones for socket 1366, but I was wondering

is there a better solution?

Answer 1

, /, , , ... .

, . ...

, , ProductProperties,

ProductId 
PropertyTypeId
PropertyId
Value

:

ProductId
ProductCategoryId
Name
etc...

, 1 1366, 2 1366, "Socket" 1366. . ProductProperties ( ), ProductCategory , , , ... , (, ), .

, , PropertyTypes , . , .

, , - . newegg, , , , - , .

Answer 2

:

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

" " ProductType. FK Products.ProductID .

Answer 3

EAV

SO EAV Q/As, .

Answer 4

What you are looking for are subclasses. One table will contain basic information that applies to all products. This is what you refer to the order table. You can then use other tables to refer to the product table and contain specific information based on the type of product. Sort of:

               Orders
                  |
               Products
              / | \
           CPUs RAM Motherboards