SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 30 of 30
  1. #26
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, made a bit of changes, including:
    • Changing various INT values to SMALLINT
    • Foreign keys added to each table, most to reference the theaterID
    • Most referential integrity done with ON DELETE CASCADE


    As I noted in the comments for each table, I had one truly unsure action and that was on the companies table, declaring a foreign key that references to the theaters table makes it a child to theaters. Though if a theater is removed, nothing should be done to the company it belonged to, so I think I have it right.

    Also, I didn't manually set indices since I am to believe the foreign key constraints place them automatically.

    Code MySQL:
    -- This on delete cascade is so if any companies are deleted,
    -- the theaters belonging to that company are also deleted.
    CREATE TABLE `theaters` (
    `theaterID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `companyID` SMALLINT NULL ,
    CONSTRAINT theaters_companyFK
    FOREIGN KEY ( companyID )
    REFERENCES companies ( companyID ) ON DELETE CASCADE,
    `name` VARCHAR( 100 ) NOT NULL ,
    `description` VARCHAR( 255 ) NULL,
    `mainImage` VARCHAR( 150) NULL,
    `address` VARCHAR( 100 ) NOT NULL ,
    `city` VARCHAR( 50 ) NOT NULL ,
    `state` VARCHAR( 2 ) NOT NULL ,
    `phone` INT( 11 ) NULL COMMENT  'Raw digits of a phone number, dashes can be added through PHP.'
    ) ENGINE = INNODB 
    COMMENT =  'Sole purpose is to hold the main attributes of a movie theater.';
     
    -- My one unsure action, no on delete/update action because if a
    -- theater is removed, it should not affect the companies table at all. 
    CREATE TABLE `companies` (
    `companyID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    CONSTRAINT companies_theatersFK FOREIGN KEY ( companyID ) 
    REFERENCES theaters ( companyID ) ,
    `name` VARCHAR( 50 ) NOT NULL ,
    `description` TEXT NULL
    ) ENGINE = INNODB
    COMMENT =  'Used to store each company''s name and ID for reference of theaters.';
     
    -- References theaters, on delete cascade so all pricing
    -- info for a deleted theater gets deleted as well. 
    CREATE TABLE `theater_pricing` (
    `theaterID` INT( 11 ) UNSIGNED NOT NULL ,
    CONSTRAINT theater_pricing_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE,
    `adult` DECIMAL(4,2) NULL ,
    `senior` DECIMAL(4,2) NULL ,
    `child` DECIMAL(4,2) NULL ,
    `3d` DECIMAL(4,2) NULL ,
    `matinee` DECIMAL(4,2) NULL 
    ) ENGINE = INNODB 
    COMMENT =  'Prices will be stored in a 10.75 format ($10.75).';
     
    -- References theaters, on delete cascade so all reviews
    -- for a theater get deleted with a theater deletion. 
    CREATE TABLE `theater_reviews` (
    `reviewID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `theaterID` INT( 11 ) NOT NULL ,
    CONSTRAINT theater_reviews_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE,
    `overall` FLOAT NOT NULL COMMENT  'Averages the other ratings',
    `reviewContent` VARCHAR( 255 ) NOT NULL COMMENT  'Since it''s mostly rating based, this is a small input field for additional comments.' ,
    `picQuality` TINYINT( 1 ) NULL ,
    `audioQuality` TINYINT( 1 ) NULL ,
    `priceQuality` TINYINT( 1 ) NULL ,
    `staffQuality` TINYINT( 1 ) NULL ,
    `restroomQuality` TINYINT( 1 ) NULL ,
    `stickyFloor` TINYINT( 1 ) NULL ,
    `seatingQuality` TINYINT( 1 ) NULL ,
    `concessionSelection` TINYINT( 1 ) NULL ,
    `concessionPrices` TINYINT( 1 ) NULL ,
    `visitAgain` TINYINT( 1 ) NULL COMMENT  'Yes/No - No=0, Yes=5'
    ) ENGINE = INNODB 
    COMMENT =  'Used to store individual reviews, all attributes are on a 1-5 integer scale.';
     
    -- References theaters, on delete cascade so if a theater
    -- is removed, all comments for it are also removed.
    CREATE TABLE `theater_comments` (
    `commentID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `theaterID` INT( 11 ) NOT NULL ,
    CONSTRAINT theater_comments_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE,
    `userID` INT( 11 ) NOT NULL COMMENT  'Who posted this comment?',
    `timePosted` INT( 11 ) NOT NULL COMMENT  'PHP Generated timestamp.',
    `title` VARCHAR( 100 ) NOT NULL ,
    `comment` TEXT NOT NULL
    ) ENGINE = INNODB
    COMMENT =  'Used to store comments specifically about a theater.';
     
    -- References theaters, all like attributes for that theater
    -- will be removed if a theater is.
    CREATE TABLE `theater_likes` (
    `theaterID` INT( 11 ) UNSIGNED NOT NULL ,
    CONSTRAINT theater_likes_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE,
    `userID` INT( 11 ) NOT NULL ,
    `likes` TINYINT( 1 ) NOT NULL COMMENT  '0 for dislike, 1 for like.'
    ) ENGINE = INNODB
    COMMENT =  'Stores all users votes whether it be like or dislike.';
     
    -- References theaters, all image references for that theater 
    -- will be removed once that theater is.
    CREATE TABLE `theater_images` (
    `theaterID` INT( 11 ) UNSIGNED NOT NULL ,
    CONSTRAINT theater_images_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE,
    `userID` INT( 11 ) NOT NULL COMMENT  'User that uploaded the image.',
    `imageLocation` VARCHAR( 150 ) NOT NULL COMMENT  'URL to the specific image.'
    ) ENGINE = INNODB 
    COMMENT =  'Used to store the locations of various images of the specific theater ID.';
     
    -- Really, we get it by now. 
    CREATE TABLE `theater_visits` (
    `theaterID` INT( 11 ) UNSIGNED NOT NULL ,
    CONSTRAINT theater_visits_theaterFK FOREIGN KEY ( theaterID )
    REFERENCES theaters ( theaterID ) ON DELETE CASCADE ,
    `userID` INT( 11 ) NOT NULL COMMENT  'What user visited theaterID'
    ) ENGINE = INNODB 
    COMMENT =  'Stores what user has visited what theater.';

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, you still have it wrong

    the companies table should not have a foreign key to the theaters table

    companies is the parent, theaters is the child, and i believe you should use ON DELETE RESTRICT (if a company goes out of business, the theaters still exist, they will just be picked up by a different company)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gah! Really didn't think it through well enough! So all should be good if I remove the foreign keys from the companies table, and set theaters to ON DELETE RESTRICT? Nice hint on the ON DELETE RESTRICT, was too wrapped up in the database part of it didn't think of the real world aspect hah, the theaters won't vanish into thin air if the company is removed :P.

  4. #29
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now as far as efficiency goes, if I were to decide that I want company_comments, and company_images. Would it be more efficient to add those tables with extremely similar structure to their theater counterparts, or should I modify the theater versions of them to include a companyID field and set both companyID and theaterID to NULL?

    Edit: After thinking it through I believe the latter would definitely be the more beneficial way, was fairly easy to setup as well.

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i believe the former is better -- add those tables with similar structure to their theater counterparts
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •