MySQL – Schema Review for Car Advertisement System

database-designMySQL

Ok, so I am carrying out a rather simple task as part of a job requiretement. Here is the task in a nutshell:

  • Design a form for creating ad for selling car
  • Form has 2 have:

    • 2 drop downs:
      • Car make
      • Car model (car model has to be dynamic, based on selected model
    • textaera for short description
    • Atleast 10 checkboxes with various properties (air con, electric windows etc)
  • Database has to be implemented in a way, so it is easy to search

Here is EER for the design I have at the moment:
database design

And for those that prefer SQL, you can find it here

So couple points I want to ask:

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

Another point, is with properties. Obviously this is a many to many relationship (many posts, can have many properties), so a relationship table is an obvious choice. But as far as searching goes, this complicates a bit, cause now for example I am searching for particular model, with particular properties I need to look up 2 tables. Is there better way? I was thinking to have an extra "text" field in the "post" table with comma separated list with all property ids, and use a `LIKE %property%" query (in addition to the relationships table). Any better solution?

And finally, just in general, any other suggestions given the specification?

Both answer's really good an helpful, but accepted the xuma202 answer, as he needs some of that sweet rep points more at the moment 😀

Best Answer

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

No, this adds redundancy to your database and this is something you generally do not want to have at all. It also does not fit with the 3rd Normal Form.

To read about Normal Forms look [here][1]

Another point, is with properties.

It is no problem and very typical to have database querys across multiple tables. (This is basically why we use databases and have multiple table and not smash everything together) Do not worry about it and please do NOT add extra redundancy with such an extra text field.

any other suggestions given the specification?

Yes, there is no need for the idproperties_post_rel column in your properties_post_rel table. You should make the both foreign keys the primary key so you can not accidentally set a property on one offer twice. Also the relationship is well enough defined by the unique combination of these FKs so no need to waste space for an extra synthetic key.

Related Question