MySQL –– Normalized Tables
written by: admin
Date Written: 1/10/09
Last Updated: 4/8/12
Much of this data came from
dynamic drive to create a
normalized table. I am still working out the details though.
Foreign Keys
Creating the Tables
CREATE TABLE positions (
id INT AUTO_INCREMENT,
name VARCHAR(128),
PRIMARY KEY(id)
);
CREATE TABLE people (
id INT AUTO_INCREMENT,
name VARCHAR(128),
default_position_id INT,
PRIMARY KEY(id),
FOREIGN KEY(default_position_id)
REFERENCES positions(id)
ON DELETE SET NULL
);
CREATE TABLE notes (
id INT AUTO_INCREMENT,
addressee_id INT,
day DATE,
note TEXT,
PRIMARY KEY(id),
FOREIGN KEY(addressee_id)
REFERENCES people(id)
ON DELETE CASCADE
);
CREATE TABLE assignments (
id INT AUTO_INCREMENT,
assignee_id INT,
position_id INT,
day DATE,
attendance INT,
PRIMARY KEY(id),
FOREIGN KEY(assignee_id)
REFERENCES people(id)
ON DELETE CASCADE,
FOREIGN KEY(position_id)
REFERENCES positions(id)
ON DELETE SET NULL
);
Inserting data into the tables
–– Add a new position
INSERT INTO positions (name) VALUES ('Door');
–– Add a new person
INSERT INTO people (name, default_position_id) VALUES ('John Smith', 1);
–– Assign John Smith to the Door tomorrow
INSERT INTO assignments (assignee_id, position_id, day, attendance) VALUES (1, 1, CURDATE() + INTERVAL 1 DAY, 0);
–– Leave John Smith a note for tomorrow (when he's on the door)
INSERT INTO notes (addressee_id, day, note) VALUES (1, CURDATE() + INTERVAL 1 DAY, 'Don\'t forget to lock up this time!');
retrieving the information
–– Who's doing what tomorrow?
SELECT people.name, positions.name FROM assignments JOIN people on people.id = assignments.assignee_id JOIN positions ON positions.id = assignments.position_id WHERE assignments.day = CURDATE() + INTERVAL 1 DAY;
Here's another way of writing pretty much the same thing. The only difference is that I specified a specific date so that executing the query will return the same results yesterday or tomorrow provided the content of the tables stays the same.
SELECT people.name, positions.name FROM assignments,people,positions WHERE people.id=assignments.assignee_id and positions.id=assignments.position_id and assignments.day='2009-04-27';
MySQL –– JOIN statements
Intro to Table NormalizationTAGS: mysql