Results 1 to 21 of 21
  1. #1
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Database Organization
    Ok, so I'm trying to figure out how to organize restaurants (several hundred of them) in a database. Restaurants could fall into several categories, so I know I will need at least 2 tables, one for the cats with unique ids, and then my restaurants with unique ids in another table. My problem lies in applying multiple categories to a restaurant, without of course creating a unique row for each restaurant for each category, such as:

    catid|cat
    1|Fast Food
    2|Take Out

    catid|restid|restaurant
    1|1|McDonalds
    2|1|McDonalds
    1|2|Arbys
    2|2|Arbys

    Now we're into thousands of rows.

    I've been told "database normalization" by a couple of people, and I've read a few articles and have not gained one step on figuring it out. Most of them explain it like you know what they're talking about but give no examples as to how you might apply it.

    Basically what I need is if a restaurant falls into 5 categories, how to get it to show up when each of those categories is displayed. I've been racking my brain over this for 2 days and just need to get it going because I have about 60 other major tasks to accomplish with this new site and this is only my second task.

    So any help in moving this forward would be greatly appreciated.

    Shawn Kerr (.com) | Disney World | SEC Football

  2. #2
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    Here's what you're looking for:

    catid|cat
    1|Fast Food
    2|Take Out

    restid|restaurant
    1|McDonalds
    2|Arbys

    (This third table matches the categories and restaurants.)
    catid|restid
    1|1
    2|1
    1|2
    2|2
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  3. #3
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Hmm, ok. So how is that much different than my example above? Just curious, because it looks like I'm now just adding a third table and still having to create all of those unique rows per restaurant.

    Shawn Kerr (.com) | Disney World | SEC Football

  4. #4
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    The only difference is that you don't duplicate data. If, for instance, you ended up adding another 100 fields for each restaurant (address info, details about the restaurant, etc.), you don't have to duplicate all that data. In your original example, all of that detail would be duplicated in your second table for each category that a merchant fits in.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  5. #5
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Ah, ok. That makes sense. Since I do plan on doing that.

    May put this one on the back burner, 600+ manual listings were going to be enough. Several thousand additional rows is not what I'm ready to do.

    Thanks Michael.

    Shawn Kerr (.com) | Disney World | SEC Football

  6. #6
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    If you make the front-end interface right, it's really pretty simple to input. Just have checkboxes for each category. I do similar things with some of my sites.

    If you have a lot of manual data entry for a site, it's usually easiest to make an efficient front-end rather than entering data directly into the database.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  7. #7
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    What michael coley has added (I got to this thread late) is called an intersect table. It is usually needed when you have a many to many relationship. You have many restaurants that will be in one or many categories.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  8. #8
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Well right now I'm doing it all in a spreadsheet to which I was then going to import. Basically I'm cataloging all of the restaurants in my city. I have no predefined idea of categories so I'm having to create them as I run across restaurants.

    Which my earlier estimations were a little off, I came up with a count of 514 from the latest phone book although I know of some that aren't listed.

    I think I'm just going to generalize each restaurant into one category and add it directly to each listing. Won't be as grand, but it'll work nearly the same.

    Shawn Kerr (.com) | Disney World | SEC Football

  9. #9
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Ok, this is really starting to aggravate the crap out of me, I can't get what I thought would be a simple insert to work.

    My table:

    Code:
    CREATE TABLE `dining` (
      `restid` int(11) NOT NULL auto_increment,
      `restaurant` varchar(255) NOT NULL default '',
      `address` varchar(255) NOT NULL default '',
      `citystzip` varchar(255) NOT NULL default '',
      `phone` varchar(50) NOT NULL default '',
      `area` varchar(255) NOT NULL default '',
      `latlong` varchar(100) NOT NULL default '',
      UNIQUE KEY `restid` (`restid`)
    ) TYPE=MyISAM AUTO_INCREMENT=1 ;
    My code:


    PHP Code:
    $rest addslashes($_POST[restaurant]);
    $address addslashes($_POST[address]);
    $citystatezip $_POST[citystatezip];
    $phone $_POST[phone];
    $area $_POST[area];
    $latlong $_POST[latlong];

    $query mysql_query("insert into dining (restaurant,address,
    citystzip,phone,area,latlong) values ('
    $rest','$address',
    '
    $citystatezip','$phone','$area','$latlong'"); 
    I keep getting the following error:


    Code:
    You have an error in your SQL syntax. Check the manual that corresponds 
    to your MySQL server version for the right syntax to use near '' at line 1
    I echoed the insert and tried it through phpmyadmin and got the same thing.


    PHP Code:
    insert into dining (restaurant,address,citystzip,phone,area,latlong)
     
    values ('Baumhowers Wings Restaraunt','3673 Airport Blvd.',
    'Mobile, AL 36608','(251) 344-2131','West Mobile','30.67673,-88.132946' 
    Where am I going wrong?

    Shawn Kerr (.com) | Disney World | SEC Football

  10. #10
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Maybe try this format:

    Code:
    insert into dining set restaurant='Rest Name', address='Address', etc.
    I find it's much easier to understand.

    Also in regard to your first question you can create a list of catids for each restuarant rather than creating a 3rd table. This list can be stored in a field as something like "123,452,211,223" and you can select restaurants in a particular category like this:

    Code:
    where catid regexp '[[:<:]]123[[:>:]]'
    This may not be as flexible as an intersect table, but it requires fewer rows and tables. Anybody ever tried this before?

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  11. #11
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    Utah
    Posts
    1,141
    Quote Originally Posted by patrick24601
    What michael coley has added (I got to this thread late) is called an intersect table.
    I always thought it was called a lookup table. Am I wrong?

    Michael

  12. #12
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    emdub,

    Good catch - he actually has both in his post. Two lookup tables and an intersect table. At least the way I've used the terms over the years. The lookup table has the end value that he wants to relate to. The intersect table actually has the keys in it for all of the lookup tables.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  13. #13
    Full Member
    Join Date
    March 10th, 2006
    Posts
    466
    $rest = addslashes($_POST[restaurant]);
    In the closest book I have here (PHP & Mysql Web Development Welling & Thompson), they use quotes in the $_POST, e.g.

    $rest = addslashes($_POST['restaurant']);
    In my own code I use double quotes:

    $rest = addslashes($_POST["restaurant"]);
    Except for the missing end bracket ")" (was that a typo?), not sure what's going on the the insert query because I don't do enough of them:
    PHP Code:
     insert into dining (restaurant,address,citystzip,phone,area,latlong)
    values ('Baumhowers Wings Restaraunt','3673 Airport Blvd.',
    'Mobile, AL 36608','(251) 344-2131','West Mobile','30.67673,-88.132946' 
    But there's a lot of commas, and dashes, and dots between the quotes. Maybe try eliminating the punctuation first...and try again.???

    Dirk Gardener's import code works quite well, I usually reference it for syntax when doing any data imports.

  14. #14
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    Utah
    Posts
    1,141
    Quote Originally Posted by patrick24601
    emdub,

    Good catch - he actually has both in his post. Two lookup tables and an intersect table. At least the way I've used the terms over the years. The lookup table has the end value that he wants to relate to. The intersect table actually has the keys in it for all of the lookup tables.
    You're absolutely right. I should have look at my own database structure before posting.

    I knew the term "lookup" but not "intersect". Thanks!

    Michael

  15. #15
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    My pleasure. And I am absolutely right... today. Tomorrow is another story

    and I agree with Mr. JOnes... that comma looks like trouble. You probably have to escape it.

    Patrick
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  16. #16
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Quote Originally Posted by mr_jones
    Except for the missing end bracket ")" (was that a typo?), not sure what's going on the the insert query because I don't do enough of them:
    Yeah, I just didn't copy everything when I copied that bit.


    Quote Originally Posted by Snib
    Maybe try this format
    That's one thing I didn't think to try. I'll try it and see what happens.

    It's just aggravating when you do something many times, and it doesn't work once. I use the same insert format in the blog I wrote, so it should work. Ah well. That's coding.

    Shawn Kerr (.com) | Disney World | SEC Football

  17. #17
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    That worked Snib. Not sure why I didn't try that but after a while of aggravation I just lost my train of thought.

    Thanks everybody.

    Shawn Kerr (.com) | Disney World | SEC Football

  18. #18
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by mobilebadboy
    That worked Snib. Not sure why I didn't try that but after a while of aggravation I just lost my train of thought.

    Thanks everybody.
    Cool, now maybe you'll never go back to using values()

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  19. #19
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Ok, another question, hopefully the last (at least during this project ).

    I've only recently learned to select from multiple tables (working with allposters datafeed and the recent project of listing restaurants), but this one has got me puzzled. I've tried a few different queries all with the same resulting "mysql_fetch_array(): supplied argument is not a valid MySQL result resource" error.

    Basically I want to pull all of the categories (dining_cats) and at the same time count all of the 'restid' from the 3rd table (dining_relation) that match the catid from the categories table. So I can display how many restaurants are in each category.

    American (5)
    Mexican (3)
    etc.

    I've screwed the query up so bad and confused myself I figured I'd turn to the smart folks here.

    Shawn Kerr (.com) | Disney World | SEC Football

  20. #20
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Your error sounds like nothing came back from your query. Make sure you test your query first in mysql only and leave php out of the picture. It just muddies the waters when you are dabbling with queries. Use phpmyadmin or my recent favorite sqlyog

    I think you want something like this - a subquery:

    select catid, categoryname, (select count(*) from dining_relation where dining_cats.catid = dining_relation.catid) as count
    from dining_cats
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  21. #21
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Yeah, that didn't work along with about 20 different queries I tried.

    Somehow, this partially works (not even sure what I was doing, lol).

    Code:
    select dining_relation.catid, count(dining_relation.restid) as count,
    dining_cats.category as category from dining_relation left outer join
    dining_cats on dining_relation.catid = dining_cats.catid group by
    dining_cats.category order by dining_cats.category asc
    The partially is because it doesn't pull categories with no restaurants listed (I want all cats listed regardless, I can always just display it textually with no link until I get restaurants listed).

    Which I know it can't based on the way the query is working.

    I'm guessing I'm going to have to separate the queries.

    Shawn Kerr (.com) | Disney World | SEC Football

  22. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. I need organization skills here
    By LindaLS in forum Newbie Affiliate FAQs & Helpful Articles
    Replies: 10
    Last Post: April 2nd, 2007, 06:02 PM
  2. Organization Is Key!
    By YouLoveToShop in forum Rakuten LinkShare - LS
    Replies: 2
    Last Post: June 2nd, 2005, 10:50 PM

Posting Permissions

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