In this code:
CREATE TABLE `institution` (
`id` SMALLINT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE announcement (
`id` INT UNSIGNED AUTO_INCREMENT,
`institution` SMALLINT UNSIGNED NOT NULL,
`content` TEXT NOT NULL,
CONSTRAINT `fk_announcement_institution`
FOREIGN KEY (`institution`) REFERENCES `institution` (`id`),
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `reaction` (
`id` BIGINT UNSIGNED AUTO_INCREMENT,
`announcement` INT UNSIGNED NOT NULL,
`user` INT UNSIGNED,
`content` TEXT NOT NULL,
CONSTRAINT `fk_reaction_announcement`
FOREIGN KEY (`announcement`) REFERENCES `announcement` (`id`),
CONSTRAINT `fk_reaction_user`
FOREIGN KEY (`user`) REFERENCES `user` (`id`),
PRIMARY KEY (`id`))
ENGINE = InnoDB;
The reaction can be added by a user or an institution (the one who created the announcement in the first place), I've decided to use the "user" column to determine the student who added the reaction and if it's null i'll determine it's the institution.
Is that's okay? to use null this way? or should i do it like this (so i left join to determine which belongs to user and which's belong to the institution):
CREATE TABLE `institution` (
`id` SMALLINT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE announcement (
`id` INT UNSIGNED AUTO_INCREMENT,
`institution` SMALLINT UNSIGNED NOT NULL,
`content` TEXT NOT NULL,
CONSTRAINT `fk_announcement_institution`
FOREIGN KEY (`institution`) REFERENCES `institution` (`id`),
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `reaction` (
`id` BIGINT UNSIGNED AUTO_INCREMENT,
`announcement` INT UNSIGNED NOT NULL,
`content` TEXT NOT NULL,
CONSTRAINT `fk_reaction_announcement`
FOREIGN KEY (`announcement`) REFERENCES `announcement` (`id`),
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `reaction_user` (
`reaction` BIGINT UNSIGNED,
`user` INT UNSIGNED,
CONSTRAINT `fk_reaction_user_reaction`
FOREIGN KEY (`reaction`) REFERENCES `reaction` (`id`),
CONSTRAINT `fk_reaction_user_user`
FOREIGN KEY (`user`) REFERENCES `user` (`id`),
PRIMARY KEY (`reaction`, `user`))
ENGINE = InnoDB;
Another question two please:
Which's better more tables and less joins or more joins and less tables?
And...
I've read in a lot of questions here in stack-overflow is that InnoDB stores null values with only 1 bit, but here in manual says: "An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page". So Who's right?
Thank you.