Results 1 to 10 of 10

Thread: table normalization

  1. #1
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default table normalization

    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.
    Last edited by james438; 01-22-2009 at 04:21 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  2. #2
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

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

    Code:
    DROP TABLE `Users`;
    CREATE TABLE `Users` (
         uid INT(16) UNSIGNED AUTO-INCREMENT,
         name VARCHAR(128),
         email VARCHAR(255),
         ...
    );
    Code:
    DROP TABLE `Positions`;
    CREATE TABLE `Positions` (
         pid INT(16) UNSIGNED AUTO-INCREMENT,
         title VARCHAR(128),
         description VARCHAR(255),
         ...
    );
    Code:
    DROP TABLE `U_has_position`;
    CREATE TABLE `U_has_position` (
         uid INT(16),
         pid INT(16),
         u_present INT(8),
         time,
         note,
         ...
    );

  3. #3
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default

    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?
    Last edited by james438; 01-16-2009 at 01:40 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  4. #4
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    Quote Originally Posted by james438 View Post
    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?
    Code:
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  6. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default

    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

    Code:
    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  7. #7
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

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

    Code:
    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.
    Last edited by Twey; 01-19-2009 at 09:05 AM. Reason: Add some NOT NULLs.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  8. The Following User Says Thank You to Twey For This Useful Post:

    james438 (01-20-2009)

  9. #8
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default

    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!
    To choose the lesser of two evils is still to choose evil. My personal site

  10. #9
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Code:
    -- 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.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  11. The Following User Says Thank You to Twey For This Useful Post:

    james438 (01-20-2009)

  12. #10
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,429
    Thanks
    104
    Thanked 117 Times in 115 Posts

    Default

    Thanks, that was very helpful
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •