sql - Can this MySQL db be improved or is it good as it is?

Question

In a classifieds website, you have several categories (cars, mc, houses etc). For every category chosen, a hidden div becomes visible and shows additional options the user may specify if he/she wishes.

I am creating a db now, and I have read some articles about normalization and making it optimized etc...

Here is my layup today

CATEGORY TABLE:
- cars
- mc
- houses

CLASSIFIED TABLE:
- headline
- description
- hide_telephone_nr
- changeable
- action
- price
- modify_date

POSTER TABLE:
- name
- passw
- tel
- email

AREA TABLE:
- area
- community

CARS TABLE:
- year
- fuel
- gearbox
- colour

MC TABLE:
- year
- type

HOUSE TABLE:
- Villa
- Apartment
- Size
- rooms
etc

I have so far one table for each category, so that is around 30 tables. Isn't that too many?

I haven't created PK or FK for any of these so far, haven't got that far yet...

Could you tell me if this setup is good, or should I have it made differently?

ALSO, how would you setup the FK and the PK here?

Thanks

Answer 1

From my understanding, I would make a table for all the categories and store the categories' name and ID there. Next, I would create a separate table to store the additional options for each category.

MySQL Table 1  
----------------   
Category_ID int PRIMARY KEY  
Category_name varchar  

MySQL Table 2  
---------------- 
Category_ID int   
Entry_Number int PRIMARY KEY (this will keep track of which entry everything belongs to)
Additional_Option varchar  
Additional_Option_Answer varchar (this is the one that stores what your user clicks/inputs)

For example, using:

POSTER TABLE:
- name
- passw
- tel
- email

You would store the category_id this data is for in Category_ID and store name passw tel email into Additional_Option in it's own row and the user's input for those criteria would be stored in Additional_Option_Answer.

Category_ID for Posters will be 1 and for Area will be 2.

It would look like this if the first user added something:

---------------------------------------------------------------------------------------------
Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
---------------------------------------------------------------------------------------------
       1      |   1                |    name                 |   doug
       1      |   1                |    passw                |   1234

It would look like this if the second user added something:

---------------------------------------------------------------------------------------------
Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
---------------------------------------------------------------------------------------------
       1      |   2                |    name                 |   Hamlet
       1      |   2                |    passw                |   iliketurtles

Further more, let's apply another category:

AREA TABLE:
- area
- community

---------------------------------------------------------------------------------------------
    Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
    ---------------------------------------------------------------------------------------------
           2      |   3                |    area                 |   San Francisco
           2      |   3                |    community            |   community_name