I want to design a database for a listing website, this is the first time I design the database, so I am not sure if I in the correct path. Here is the fields that needed in a listing,

  1. title - Character
  2. pub_date -Timestamp
  3. modi_date -Timestamp
  4. ex_date - Timestamp
  5. desc - Character
  6. thumb - Character
  7. office_no - Integer
  8. mobile_no - Integer
  9. email - Character
  10. web - Character
  11. status - Character
  12. state - Character
  13. city - Character
  14. address - Character
  15. postcode - Character
  16. lat - float
  17. lng - float

The lat (latitude) and lng (longitude) are used for searching, eg, searching nearby listing by comparing the lat and lng. So these fields will be queried often.

My question is should I separate the lat and lng into another table (normalization) since these fields will be query a lot. Or I just leave it in one table.

  • Which DBMS are you using? Some DBMSes support spatial datatypes. It might be worth looking into using those. – Colin 't Hart Sep 16 '15 at 18:32
  • 4
    Separating fields "because of performance" is not normalization. Usually having good indexes is the key. – jkavalik Sep 16 '15 at 18:32
  • @Colin'tHart, i think using the Relational databases, since postgresql is in my consideration. – dev-jim Sep 16 '15 at 18:43
  • @jkavalik, can you please elaborate more? – dev-jim Sep 16 '15 at 18:43
  • @dev-jim normalization will help you get rid of redundancy and insert/update/delete anomalies. lat and lng columns in your table do not seems to suffer from these so moving them to a separate table is not "normalization". On the other hand it is still a valid move and may be even beneficial in some use cases - imagine you have a table of cars with gps locators and keep their actual position in those columns - moving them to a separate table would imho allow you making more updates per second to those columns without affecting the remaining data. But for SELECTs you need proper indexing. – jkavalik Sep 16 '15 at 19:02

Your Answer

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

Browse other questions tagged or ask your own question.