开发者

How to set up parent and child tables (per category properties) in MySQL?

I have a classified ad website. Users may put classifieds in, and each category has sub options.

Example: the user picks "car", and has to fill in options such as color, mileage, fuel, gearbox etc.

Here are my tables for this:

Category table:
cat_id (PK)
cat_name // "Cars" for exampl开发者_开发技巧e

Category Options table:
option_id (PK)
cat_id (FK)
option_name // "Color" for example

Option Values table:
value_id (PK)
option_id (FK)
value // "red" for example

My question is, in the last table (option values), shouldn't there be "something else" too, because how do I increase this table or reference this table to the classified?

For example, let's look at two records in these tables. First is Category table:

  Cat_id         Cat_name
     1            cars
     2            trucks

Then there is the Category Options table:

 option_id        Cat_id(FK)       option_name
     1              1                color
     2              1                mileage

Then the option values table, here is the problem, I don't know how to :

  Value_id       option_id(FK)    value
     1               1            red
     2               2          11000 miles

Then what happens if I decide to insert another advert with another car? Shouldn't there be another reference in the last table?

I also have other tables such as "classified" which contains headline, text, price etc.


You already gave the answer yourself.

Shouldn't there be another reference in the last table?

I also have other tables such as "classified" which contains headline, text, price etc...

Put the ID of the table classified as another key in the table options, so that you get

Option Values table:
value_id (PK)
option_id (FK)
classified_id (FK)
value // "red" for example

Then you can get all values for all options for a specified classified:

SELECT c.option_name, o.value
FROM option_Values o
LEFT JOIN category_options c ON (o.option_id=c.option_id)
WHERE o.classified_id = 15; -- as an example
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜