0

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 :D

  • Welcome to Code Review! Can you please include the code you use to create your database/schema? We can't "code review" only a schema diagram, since it's not code... – Phrancis Apr 22 '15 at 17:30
1

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.

  • Thanks for your review! Same question as to the other person, can you point me where I can read more about these 2 column PKs? As I tried googling, but could not find anything! – galdikas Apr 22 '15 at 17:52
  • look here or here you can simply use them like normal primary key. There is an example on the 2nd link. However the important part is PRIMARY KEY(user_id,role_id) – xuma202 Apr 22 '15 at 17:58
  • Ok so is my thinking correct: I created 2 columns, and set them both as primary key. Now I just add the correct foreign keys to get the relationship, like so: pastebin.com/fLjk0Xjf – galdikas Apr 22 '15 at 18:03
  • Yes that is correct – xuma202 Apr 22 '15 at 18:04
1

here's my observations. :)

1/ Your posts should only refer to a car_model. There is almost no performance issues to do an additional join given the fact that there won't be a load of car makers.

More about that, you are running into an integrity problem : what if you have a problem with an update and found that your post is referring to an 'Audi A3' but is referring to a car maker 'Ford' ?

2/ Having a many-to-many relationship is the best way you can achieve what you want to do. Creating a text field with comma separated values will not assure you integrity in your database and worse of all, a search with LIKE '%value%' is a HORRIBLE performance issue ! Never ever think about this idea. Really.

With your design, you may have multiple times 'Air conditioning' for a post. That's not what you want because this is another integrity issue. Your many-to-many relationship should have a 2-column primary key. With MySQL Workbench, you can use identifying relationships instead of non-identifying relationships.

That's all for me :)

  • Thank you for review! Could you actually point me to somewhere, where I could read about how to make the 2 column primary key? As I have actually thought about this, but then couldn't find any info how to actually make it. – galdikas Apr 22 '15 at 17:50
  • With MySQL Workbench, you may use n:m relationships (last button on the left side). In MySQL, you should have something like this : CREATE TABLE properties_posts_ref (post_id INT NOT NULL, property_id INT NOT NULL PRIMARY KEY(post_id, property_id)) – DeadEye Apr 22 '15 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.