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

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

Answers


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

Need Your Help

special character encoding C# and Ironpython

c# special-characters ironpython

I am facing an encoding issue while trying to pass a string from two C# modules using Ironpython code as a bridge.

How do I calculate and use a Morton (z-index) value to index geodata with PHP/MySQL?

php mysql geolocation indexing z-index

I have a MySQL table of records, each with a lat/lng coordinate. Searches are conducted on this data based on a center point and a radius (any records within the radius is returned). I'm using the

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.