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
精彩评论