Is there any tool/SW to help me build a good database ?

Question

I am new to databases. I have a classifieds website with MySQL db and I am soon about to use SOLR to index them also. Then whenever a query is done, SOLR will return ID:s and I will match those ID:s to the MySQL database and fetch the ads to display.

Anyways, I have trouble making the db.

Users may choose from a drop-list what category to search. Lets say they choose CARS. Then a div is shown containing drop lists for "YEAR, FUEL, GEARBOX, PRICE". Then they may choose LOCATION also. Also if they wish, they may type in a querystring. All is optional though.

How should I setup this?

For example, I have this below for CARS right now, and I have to make fields/tables for Motorcycles, trucks, scooters also... But so far I have got only for the CARS. How would you do the structuring and designing? I am thinking about doing one of these for each category (TRUCKS, MC, HOUSES etc).

SORRY, but I can't get the tabs to work here, could anybody edit this so it looks "more readable" please!

Fält            Typ         Null    Standard <br>

id          int(7)          Nej 
ad_id           varchar(62) Nej 
area            varchar(40) Nej 
area_community  varchar(50) Nej 
price           int(9)          Nej 
year            int(4)          Nej 
mileage         int(6)          Nej 
gearbox         varchar(12) Nej 
fuel            varchar(12) Nej 
insert_date timestamp   Nej CURRENT_TIMESTAMP
poster_name varchar(40) Nej 
poster_email    varchar(50) Nej 
poster_tel  varchar(20) Nej 
poster_password varchar(15) Nej     
headline    varchar(40) Nej 
description text            Nej 
salebuy         varchar(7)  Nej 
total_pics  int(2)          Nej 
changeable  int(1)          Nej 
hide_tel    int(1)          Nej 
ad_category varchar(60) Nej 

Thanks and if you need more input just let me know and I will update this Q.

Answer 1

I suggest you create something on the lines of:

1. Category table
CategoryID -> Primary key
CatName    -> truck, location, small ad etc

2. CategoryPropertyPermitted Table
CategoryID -)
PropertyID -) Primary key
PropName   -> year, mileage, colour etc

3. Persons Table
PersonID  -> Primary key
PersonSurname
DateAdded
Etc

4. Ad Table
AdID  -> Primary key
PersonID
CategoryID
Notes
DateAdded
Etc

5. PersonCategoryProperty
ProperytyID -)
CategoryID  -)
AdID        -) Primary key
PropertyDetails -> 1967, red, ford
Etc

Answer 2

To assist you in creating and visualizing, I would use mysql workbench.

Next, start with Remou's comments. He's got the poster, the ad, and the product all separated out. Then think about the likely details people would want to post for each one (gearbox, etc, as you mentioned).

Now is the tricky part -> how much control do you want to give a poster? And how good do you want your result set to be?

If you want a precise result set, make another table for each element, and a foreign key for each for the corresponding item in the cars table. Then each item will have a list of allowed entries, and if you trust your users, they can add new ones. This means that everyone will select from the same options, and that will lead to more consistent results (for example, someone might say 4.5 liters, some might say 4.5L, so having it selected from a drop down eliminates the dupes in terminology). This will also make your posting a little more complex, but actually easier for the end user since it will mostly be drop downs.

Otherwise, you could just allow text for each of the items in the cars table, but your query results are more likely to be missing results.

In the end, do some research on database normalization, and try breaking everything up into chunks, as Remou indicated.