ON DELETE CASCADE makes sense
as for ON UPDATE, i would make that CASCADE as well, but you could also make it RESTRICT
when would you need to cascade an update? you would need it if you ever decided to change the id of a theater from one number to a different number
why would you do that, you ask? that’s a different question, isn’t it 
So, as I’m wrapping things up and about to make all the appropriate changes to the database structure, I’m just a bit stuck on a small portion of the foreign keys.
Say I have my theater_reviews, with a foreign key of theaterID referencing the theaters tables theaterID. If I set ON DELETE CASCADE, then when a review is deleted, it’s deleted from the theater_reviews (parent table), and any matching rows in the theaters table (child) are also deleted. Now shouldn’t that be 0 rows in the theaters table are deleted if a review is deleted? If so, is there any purpose to setting ON DELETE/UPDATE CASCADE on any tables besides theaters-companies?
i am afraid you have this completely backwards 
when a row in the theater table (the parent table with the primary key) is deleted, then all related rows in the theater_reviews table (the child table, with the foreign key that references the theaters table) are also deleted if ON DELETE CASCADE is set
if you just delete a review, this has no effect on the parent theater
yes, on all the tables linked to a theater, so that if you delete a theater, everything related to it also gets deleted – pricing, reviews, comments, likes, images…
Ah, see my issue was I was assuming that the table with the constraint referencing another table was considered the parent, where it’s the complete opposite, whoops.
So anytime a table is creating a foreign key it is the child to the table it’s referencing, gotcha. (Hope I didn’t get that wrong and you have to give me another backwards reaction :P)
correct
Ok, made a bit of changes, including:
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.';
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)
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.
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.
i believe the former is better – add those tables with similar structure to their theater counterparts