MySQL linking tables; some final issues

Question

Firstly I want to thank all of you who have helped me understand Normalization and how I should build up my database.

Now, I have some final issues left...

How does LINKING of tables actually work?

Say you have three tables:

CATEGORY TABLE:
cat_id (PK) -> 1
cat_name -> cars

CATEGORY_OPTIONS TABLE:
cat_opt_id (FK) -> 1
cat_id (FK) -> 1
option_name -> year

CATEGORY_OPTIONS_VALUES TABLE:
cat_opt_val_id (PK) -> 1
cat_opt_id (FK) -> 1
value -> 1999

Basically, the values should look like this:

CATEGORY
(1, cars)
(2, MC)

CATEGORY_OPTIONS
(1, 1, year)
(2, 1, fuel)
(3, 2, type)

CATEGORY_OPTIONS_VALUES
(1, 1, 2010)
(2, 1, Petrol)
(3, 2, Cross)

Is this correct as I have it setup above?

How would I search these, how is the logic made up?

I think I need examples of queries from PHP (SELECT etc)

Say you want to search for a CAR -> year=2010, fuel=PETROL how is the query then? AND SAY you want to search for a CAR -> fuel=PETROL, year=anything

LASTLY, should I use AutoIncrement on any of these fields? And when is AI used?

Thanks

PS: For more info, check out this Q: http://stackoverflow.com/questions/2100008/can-this-mysql-db-be-improved-or-is-it-good-as-it-is

Answer 1

You want SQL joins. Table linking is a completely unrelated technique that has nothing to do with what you are trying to achieve (for details, see MySQL documentation on the MERGE engine). Generally:

SELECT f.name, b.value
FROM foo f
LEFT JOIN bar b ON b.foo_id = f.id
WHERE f.age > 10
HAVING b.value IS NOT NULL

In order to understand how joins are performed, you must understand how the database engine processes a query - especially the significance of conditions in ON, WHERE and HAVING clauses which all apply at different stages.