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.
Could someone please give me some pointers?
Thank you in advance!
WHERE city = 'Paris'
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:
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
id | country_id | name
1 | 1 | Paris
2 | 1 | Lyon
3 | 2 | New York
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
city_id = 1
Hi r937 "Rudy" and ScallioXTX,
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.
Happy to hear it. Feel free to come back with more questions
This topic is now closed. New replies are no longer allowed.