I have some questions about how to design my table. My website will be a directory of top restaurants in the world. I’m going to have city-specific pages that will list the restaurants in that city. So, I could have a page with 10 restaurants in Paris. Each listing includes a link to that restaurant’s own page which provides more information about that restaurant.
Should I have one huge table with all of the restaurants, with for example, 5000 rows or have city-specific tables. If I use the former, how would I code my select queries to extract the city-specific rows? The latter would probably be pushing it, but from an organizational standpoint it would be nice to have a table with just the info for that city.
I would create one big table for all restaurants. 5000 rows is nothing for MySQL, don’t worry about that.
As for the cities, I would create a separate table for that, and then point from the restaurants table to the cities table. Something like:
Table: restaurant
id | city_id | name
----------------------------------
1 | 1 | The blue oyster
2 | 1 | The red lobster
3 | 2 | The pink salmon
3 | 3 | The black rib eye
Table: city
id | country_id | name
----------------------------------
1 | 1 | Paris
2 | 1 | Lyon
3 | 2 | New York
Table: country
id | name
---------------------
1 | France
2 | USA
Of course you would add more columns to all tables (like number of tables for a restaurant, number of inhabitants per city, language per country, etc) but this is the basic gist of it.
Then when you want all restaurants in Paris, just do
SELECT
id
, name
, etc
, etc
FROM
restaurant
WHERE
city_id = 1
thank you very much for the information. I appreciate the help.
So one big table it will be and then separate tables for some of the other columns. I’m still in the process of normalizing my database, which is a new concept to me, so I’ll probably require more help in the near future.