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.
-- 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.';