Need help with normalizing my database

Question

I am woundering whether someone has time to normalize my database?

I dont know if it needs normalization, but I want to do everything perfectly, and I dont know if it is already good or not.

I have used phpMyAdmin to create my database...

Background: Im making a classifieds website. One category of ads is 'cars'. So far, I have created the database for cars only, i thought it might be good to begin with cars simply...

Here is the table: (only one table)

1. id (AI) (INT(7))
2. area_id (VARCHAR(30))
3. area_community (VARCHAR(30))
4. price (INT(9))
5. year (INT(4))
6. mile (INT(6))
7. gearbox (VARCHAR(12))
8. fuel (VARCHAR(12))
9. insert_date (TIMESTAMP)
10. poster_name (VARCHAR(30))
11. poster_email (VARCHAR(30))
12. poster_tel (VARCHAR(20))
13. poster_password (VARCHAR(15))
14. poster_zip (INT(5))
15. private_or_company (INT(2))
16. headline (VARCHAR(60))
17. description (TEXT)
18. sell_or_buy (INT(2))
19. nr_of_pics (INT(2))

I will update this post everytime you need more input if thats the case... I have read a couple of articles about normalization, I get the concept, but dont really know how to put it to practice.

Thanks alot!

UPDATE OK, so splitting out is the deal here... So say I have splitted poster out to a separate table. How do I link the 'poster_table' to the first table? That is, if the user clicks an ad, and then the ad will have to be displayed, along side poster name, date, area etc etc... So there has to be a way to link them, am I right?

Answer 1

The easiest - take the poster fields to a different table, and just save its id. I guess that the same person may post more than one ad.

Other than that - what are the repetitions? gearbox and fuel are candidates for normalization either.

Answer 2

Kinda vague but at minimum you should have the following tables:

items - corresponding to any item in the classified
users - person either buying or selling
addresses - any address
images - holds an item image
item_features - hold item specific info like engine, model, etc for a car

Answer 3

Some comments:

Split out Area, and Poster to separate tables.

I would use a date type rather then a timestamp for insert_date. (But it depends how you're using it.)

You could also split out gearbox and fuel, as they're almost certainly going to be duplicated a lot.

Your width's for names and email addresses are also too small.

Answer 4

I'd advise to figure out all the features you will want and then start planning the DB. Otherwise right now the table is fine - as long as its just for cars. If you will want other types of items then this will not be adequate and you will have to either make more tables (one for each type of items) or do more normalization.

Added: At a second glance, you do need to move posters out to a separate table because they are like users with passwords and stuff. Unless you want a person to make a new login for every record, you should have it in a separate table. Otherwise thing what happens when a person changes their password - you have to change it in all the rows in your table! That's pretty awkward and inefficient. The basic idea is - every piece of data should be stored in one place only. If, in order to change a single piece of data, you have to update several copies of this data, then you should move it out to another table and make foreign keys.

Added 2: On the other hand - maybe you don't want your users to make a separate logins? Maybe they just post their ads and set a password for each one of them (and then use the password to edit the specific item)? In that case you should not separate the two tables. If you have two tables where the records go 1:1 (as in, for each row in first table there is no more and no less than 1 row in the second table), then the tables should be merged.

Answer 5

If you look at your current table you have at least four concepts bundled in it. I would make separate tables for the following concepts:

1. Category
2. Ad
3. Seller (what you call 'poster')
4. Area
5. Images

I would connect the ads with categories with a many-to-many relation, the ads with the sellers with a many-to-one, and either relate the area with the ads with a many-to-many, or do the same with sellers and areas, depending on what sort of design you're after. Images would be linked to ads using a one-to-many relation.

Good luck!

Answer 6

Okay:

1. Fields 10 through 15 (having to do with the poster) go into a separate table. Add a primary key called PosterID to that table, and replace fields 10 through 15 with the field PosterID as a foreign key back to the poster table.

2. Do the same for fields 2 & 3, assuming that area_community is a description of the area_id. Also, consider whether area is a property of the poster or the individual advertisement.

3. Fields 5 through 8 are problematic because they make this table one for cars, not just advertisements. I would either: A) split this into two tables (Ad and Ad_Cars) or (better) B) create tables Categories, CategoryProperties, and AdProperties. Categories lists things like car, furniture, etc. CategoryProperties lists the properties each category can or must have ({car, miles}, {car, gearing}, {car, make}). Finally AdProperties is populated by taking the right set of properties from CategoryProperties, linking them to an Ad (by ID) and having the user fill in values ({1, miles, 10000}, {1, gearing, manual}).

4. Not sure how you're handling pictures, but I'd probably ad a table AdPictures that contains AdID and PictureFileName. I'd get rid of nr_of_pictures and instead get that info from a GROUP BY view on AdPictures, unless this causes a performance problem

Answer 7

Some education on what normalizing is:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx