I'm coding a classifieds ad web application.
What's the optimal method to structure the database with this? Due to our prime repeatability, will it be faster (when it comes to searching/indexing) to possess a separate table within the database for every city?
Or will it be okay to simply have one table for each city (it might have lots of rows..).
The classifieds table has id, user_id, city_title, category,[description and detail fields].
Instead of developing a seperate table for every City, I'd produce the following tables:
Advert Category City User
Advert may have Foreign Secrets to Category, City and User. It will likewise contain particulars concerning the item.
City may have an ID and every one of the required Metropolitan areas. The ID would be the Foreign Key for that Advert table.
Category would be the just like City with Category particulars.
User may have an ID and particulars from the user. The ID would be the Foreign Key for that Advert table.
You need to split up the sun and rain inside your table into 4 tables.
The very first table, a town table, might have city_id and city_title. This table would want a distinctive index on city_title, city_id, in addition to a primary index on city_id.
The 2nd table, a category table, might have category_id, and category. This table would want a distinctive index on category, category_id, in addition to a primary index on category_id.
The 3rd table, a person table, might have user_id, user identification.
The 4th table, an advertisement table, might have ad_id, title, description, and date_placed. City_id, category_id, and user_id could be foreign secrets for your ad table,