How to organize MySQL table and retrieve specific records

Databases
1

Hi all,

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!

2

the former

SELECT name
     , cuisine
     , rating
  FROM restaurants
 WHERE city = 'Paris'
3

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
4

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.

Thanks again.

5

Happy to hear it. Feel free to come back with more questions :slight_smile: