table normalization

[james438]

I am looking to get a table normalized. The current format has

a list of names,
the position of the person for that day,
a potential note for each person for each day,
5 attendance checks taken during each day to just to see if they were present. a yes or no value.,
default data for the position of each person

Previous days will have the person's rank listed for that day. Names are often added, removed, or updated. Position names are often altered.

How would this look normalized? I have tried doing this myself, but I just can't seem to figure it out. If you need more information please let me know.

[boogyman]

1 table for user personal info
1 table for position info
1 table to reference user at position

DROP TABLE `Users`;
CREATE TABLE `Users` (
     uid INT(16) UNSIGNED AUTO-INCREMENT,
     name VARCHAR(128),
     email VARCHAR(255),
     ...
);

DROP TABLE `Positions`;
CREATE TABLE `Positions` (
     pid INT(16) UNSIGNED AUTO-INCREMENT,
     title VARCHAR(128),
     description VARCHAR(255),
     ...
);

DROP TABLE `U_has_position`;
CREATE TABLE `U_has_position` (
     uid INT(16),
     pid INT(16),
     u_present INT(8),
     time,
     note,
     ...
);

[james438]

Thank you for your response and help.

I have not used a JOIN or the like before, but I understand the basic concept. What would the query look like to extract all of the information for all of the employees for a particular day so that the information could be displayed in the form of a spreadsheet or chart? I am curious how the query would look because I do not see how I could be sure that the info pulled would all be related to one particular day.

Why is there a command to drop the table right before it is created?

[boogyman]

What would the query look like to extract all of the information for all of the employees for a particular day so that the information could be displayed in the form of a spreadsheet or chart?

SELECT tbl1.field, tbl1.field, tbl2.field, tbl3.field FROM table1 AS tbl1 LEFT JOIN table2 AS tbl2 ON tbl2.field=tbl1.field LEFT JOIN table3 AS tbl3 ON tbl3.field=(tbl2/tbl1).field WHERE time>(sometime) AND time<(sometime)

Let me know if you need that explained anymore.

Why is there a command to drop the table right before it is created?

It's not necessary if those tables are not already in the database. The drop table statement would just delete the table and any records that were associated with it. Some front end clients use this method when performing an Sql Dump, to ensure that when the file is "uploaded" there won't be an error thrown due to incorrect fields or declarations.

[james438]

thanks, that gives me something to chew on for a while. I may be back later after I have tried a few practice runs with it, but this should help me to get a good start on it.

[james438]

Still trying to wrap my mind around some of this. When inserting the data into the database and considering that names are added and subtracted (or spelling is corrected) rather frequently, what would be the best way to correlate the data with the name? From what I can see there would have to be a field for the person's name in tables 1 and 3 and the tables would have to be updated at the same time whenever a change needs to be made to the roster. I can do that easily enough. Just not sure if that is the proper way I should go about it.

Looking at your query I have tried to simplify it for what I want to do. I'll probably make it more efficient later, but using the tables that you have listed could the query look like

SELECT * FROM table1 LEFT JOIN table3 ON tbl1.name=tbl3.name WHERE time=sometime

sorry to butcher your code, but I need to start off a little simpler.

From what I can tell table 2 is not correlated with a user, but is a list of the different positions with descriptions of each position. If it were to be correlated with the names then it looks like there would be another field that would need to be added which has the employee names, which would make three names that need to be simultaneously updated and altered every time a name is added or subtracted. Names are added and deleted on a daily basis.

I am also unfamiliar with the division sign that you used: (tbl2/tbl1).field.

[Twey]

I think it was meant as a slash: 'table one or table two, depending on the query'.

CREATE TABLE positions (
  id INT AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,

  PRIMARY KEY(id)
);

CREATE TABLE people (
  id INT AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  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 NOT NULL,
  day DATE NOT NULL,
  note TEXT NOT NULL,

  PRIMARY KEY(id),
  FOREIGN KEY(addressee_id)
    REFERENCES people(id)
    ON DELETE CASCADE
);

CREATE TABLE assignments (
  id INT AUTO_INCREMENT,
  assignee_id INT NOT NULL,
  position_id INT NOT NULL,
  day DATE NOT NULL,
  attendance INT NOT NULL, -- bitset, to allow easy addition/removal of checks

  PRIMARY KEY(id),
  FOREIGN KEY(assignee_id)
    REFERENCES people(id)
    ON DELETE CASCADE,
  FOREIGN KEY(position_id)
    REFERENCES positions(id)
    ON DELETE SET NULL
);

Only one update for names. Removal of a person cascades; removal of anything else sets null. Fully normalised.

[james438]

I think it was meant as a slash: 'table one or table two, depending on the query'.

Ah, that would make sense.

I have been studying your example Twey and I pretty much did a copy and paste of your example into my own database. It just looked exactly like what I needed.

Correct me if I am wrong, but I think that I could add ON UPDATE CASCADE on each line after it says ON DELETE CASCADE in case I want to go back and fix a mistake.

It looks like the tables are nested somehow or have parent/child relationships. How would the data be inserted using this format and how would it be retrieved. I might be able to figure out how it would be retrieved, but I figure I better ask.

Thanks again!

[Twey]

-- 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!');

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

You can certainly use an ON UPDATE clause, but I'm not entirely clear on what you expect it to do, and I think it might behave differently to how you think. Have a look a the MySQL foreign key docs before jumping into anything.

[james438]

Thanks, that was very helpful