SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to organize MySQL table and retrieve specific records

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Should I have one huge table with all of the restaurants, with for example, 5000 rows or have city-specific tables.
    the former

    Quote Originally Posted by RedBishop View Post
    If I use the former, how would I code my select queries to extract the city-specific rows?
    Code:
    SELECT name
         , cuisine
         , rating
      FROM restaurants
     WHERE city = 'Paris'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,087
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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:

    Code:
    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

    Code sql:
    SELECT
        id
      , name
      , etc
      , etc
    FROM
       restaurant
    WHERE
       city_id = 1
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,087
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RedBishop View Post
    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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •