little mysql help here please

Question

I have a classified webs.

Users may put classifieds in, and each category has sub options.

Ex: 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 example

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 Q 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 classified 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...

Thanks

Answer 1

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