I am coding a classifieds ad web app.
What is the optimal way to structure the database for this? Because of the high repeatability, would it be faster (in terms of searching/indexing) to have a separate table in the database for each city?
Or would it be okay to just have one table for every city (it would have a lot of rows..).
The classifieds table has id, user_id, city_name, category,[description and detail fields].
Rather than creating a seperate table for each City, I would create the following tables:
Advert
Category
City
User
Advert will have Foreign Keys to Category, City and User. It will also contain details about the item.
City will have an ID and all of the necessary Cities. The ID will be the Foreign Key for the Advert table.
Category will be the same as City with Category details.
User will have an ID and details of the user. The ID will be the Foreign Key for the Advert table.
You should break up the elements in your table into 4 tables.
The first table, a city table, would have city_id and city_name. This table would need a unique index on city_name, city_id, as well as a primary index on city_id.
The second table, a category table, would have category_id, and category. This table would need a unique index on category, category_id, as well as a primary index on category_id.
The third table, a user table, would have user_id, user identification.
The fourth table, an ad table, would have ad_id, title, description, and date_inserted. City_id, category_id, and user_id would be foreign keys to your ad table,