Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: Help with database design

    I'm putting together a mysql database to drive a web app i'm working on to keep track of your daily intake of calories, carbs, etc...)

    A quick explanation of my design intentions:
    I want to store an entry(date, time, item) for everything I eat. The item can be either a single item or a combo.

    I have created a table for items and table for combos. A combo is several items that have been packages together. The combo use another table to track which itms are in a combo.

    here is my db structure and a few insert statements to put it into context
    Code:
    -- ------------------------------------------------------
    -- Table structure for item 
    -- -------------------------------------------------------
    CREATE TABLE item (
      `item_id` int(11) NOT NULL AUTO_INCREMENT,
      `item_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
      `item_desc` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
      `item_calories` int(4) COLLATE utf8_unicode_ci,
      `item_carbs` int(4) COLLATE utf8_unicode_ci,
      `item_fat` int(4) COLLATE utf8_unicode_ci,
      `item_protein` int(4) COLLATE utf8_unicode_ci,
      PRIMARY KEY (`item_id`)
    ); 
    
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs ) VALUES ('Advocare Fiber Drink', 'Advocare Fiber Drink Dietary Suppliment', 70, 17);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Advocare Meal Replacement Shake (Choc Mocha)', 'Advocare Meal Replacement Shake - Chocolate Mocha', 220, 24, 3, 24);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Advocare SPARK Energy Drink', 'Advocare SPARK Energy Drink', 45, 11, 0, 0);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Slice Wheat Bread(Giant Eagle)', 'Giant Eagle Whole Wheat Calcium Rich Bread', 100, 20, 1.5, 5);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Peanut Butter(2Tbsp Jif-Creamy)', 'JIF Creamy Peanut Butter', 190, 140, 16, 7);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Jam (1 Tbsp Smuckers Concord Grape)', 'Smuckers Concord Grape Jam', 50, 13, 0, 0);
    
    INSERT INTO  item(item_name, item_desc, item_calories, item_carbs, item_fat, item_protein ) VALUES ('Chicken Caeser Salad', 'Description of Chicken Caeser Salad', 300, 16, 4, 8);
    
    
    -- ------------------------------------------------------
    -- Table structure for combo - 
    -- -------------------------------------------------------
    CREATE TABLE combo (
      `combo_id` int(11) NOT NULL AUTO_INCREMENT,
      `combo_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
      `combo_desc` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`combo_id`)
    ); 
    
    
    INSERT INTO  combo(combo_id, combo_name, combo_desc) VALUES (1, 'PB&J Sandwich', 'PB&J Sandwich');
    
    
    -- ------------------------------------------------------
    -- Table structure for jobs 
    -- -------------------------------------------------------
    CREATE TABLE combo_items (
      `ci_id` int(11) NOT NULL AUTO_INCREMENT,
      `ci_combo_id` int(11) NOT NULL,
      `ci_item_id` int(11) NOT NULL,
      PRIMARY KEY (`ci_id`),
      FOREIGN KEY(`ci_combo_id`) REFERENCES combo(`combo_id`),
      FOREIGN KEY(`ci_item_id`) REFERENCES item(`item_id`)
    ); 
    
    INSERT INTO  combo_items(ci_combo_id, ci_item_id) VALUES (1,4);
    INSERT INTO  combo_items(ci_combo_id, ci_item_id) VALUES (1,4);
    INSERT INTO  combo_items(ci_combo_id, ci_item_id) VALUES (1,5);
    INSERT INTO  combo_items(ci_combo_id, ci_item_id) VALUES (1,6);
    
    -- ------------------------------------------------------
    -- Table structure for jobs 
    -- Standard format: HH:MM:SS '12:10:00'
    -- Extended hour format: HHH:MM:SS '340:10:00'
    -- -------------------------------------------------------
    CREATE TABLE entry (
      `entry_id` int(11) NOT NULL AUTO_INCREMENT,
      `entry_date` date NOT NULL,
      `entry_time` time NOT NULL default '00:00:00',
      `entry_combo_id` int(11) DEFAULT NULL,
      `entry_item_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`entry_id`),
      FOREIGN KEY(`entry_combo_id`) REFERENCES combo(`combo_id`),
      FOREIGN KEY(`entry_item_id`) REFERENCES item(`item_id`)
    ); 
    
    INSERT INTO  entry(entry_date, entry_time, entry_item_id ) VALUES ('2012/11/13','8:00:00', 1);
    
    INSERT INTO  entry(entry_date, entry_time, entry_item_id ) VALUES ('2012/11/13','8:30:00', 3);
    
    INSERT INTO  entry(entry_date, entry_time, entry_item_id ) VALUES ('2012/11/13','13:30:00', 2);
    
    INSERT INTO  entry(entry_date, entry_time, entry_item_id ) VALUES ('2012/11/13','18:00:00', 2);
    
    INSERT INTO  entry(entry_date, entry_time, entry_combo_id ) VALUES ('2012/11/13','21:00:00', 1);
    Question 1) does it make sense to store the combo items like that?

    Question 2) If I store them as it is how to I join the entry table with both the combo and item tables so I can get the names and calories from both types of entries? If I use something like this I don't get anything back because an entry does not have both an item and a combo
    Code:
    -- query entry and their item/combo name
    select entry.entry_date, entry.entry_time, item.item_name 
    from entry 
    inner join item
    on entry.entry_item_id = item.item_id
    inner join combo
    on entry.entry_combo_id = combo.combo_id;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    question 1 -- the auto_increment is useless

    change this --
    Code:
    CREATE TABLE combo_items (
      `ci_id` int(11) NOT NULL AUTO_INCREMENT,
      `ci_combo_id` int(11) NOT NULL,
      `ci_item_id` int(11) NOT NULL,
      PRIMARY KEY (`ci_id`),
      FOREIGN KEY(`ci_combo_id`) REFERENCES combo(`combo_id`),
      FOREIGN KEY(`ci_item_id`) REFERENCES item(`item_id`)
    );
    to this --
    Code:
    CREATE TABLE combo_items 
    ( ci_combo_id INTEGER NOT NULL
    , ci_item_id INTEGER NOT NULL
    , PRIMARY KEY (ci_combo_id, ci_item_id)
    , FOREIGN KEY(ci_combo_id) REFERENCES combo(combo_id)
    , FOREIGN KEY(ci_item_id) REFERENCES item(item_id)
    );
    question 2) you need a UNION
    Code:
    SELECT entry.entry_date
         , entry.entry_time
         , item.item_name 
      FROM entry 
    INNER 
      JOIN item
        ON item.item_id = entry.entry_item_id
    UNION all  
    SELECT entry.entry_date
         , entry.entry_time
         , item.item_name 
      FROM entry   
    INNER
      JOIN combo_items
        ON combo_items.ci_combo_id = entry.entry_combo_id
    INNER 
      JOIN item
        ON item.item_id = combo_items.ci_item_id
    you will also benefit from replacing your two date and time columns with a single datetime column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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