Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    4

    Multiple different dates in one table

    Hi, I'm having doubts of how a database has been designed for a project, I got 2 tables, one Message and another User

    A User can recieve many Message and a Message can be send to multiple Users, which requires a table in between to break the many to many relation. That one called MessageUser stores 2 kind of things

    - Type of user (like main or CC)
    - Multiple columns that indicate when the User performed some action on the Message (a column called Accepted that has the date when the Message was accepted, a column called Deleted that has the date when it was deleted, a column called Modified that has the date when it was modified, etc, etc, etc...)

    What it seems it was done is that a 4th table called MessageUserStatus was created which is also a one-to-many table between Message and User, which contains all the dates and leaves MessageUser just with the type of user thing.

    I'm aware something is wrong because I have insert and updates that modify just one field, and queries that require me to join the 2 tables... but I'm just a programmer pawn and maybe I'm wrong.

    Now, this are my 2 questions:

    - Is correct to have all the events registered like that or it should be a table called Event that says the type of event and the date it occured?

    - Splitting a table in 2 where both have the same relationships helps somehow? is this a secret technique for separatyng big data from small data? It doesn't violate any of the 3 norms, but I always got taught that one object should always map to one and only one entity in a db.

    Thanks in advance to everyone

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Are you sure there is not additional data being stored in the MessageUserStatus table?

    For instance, if a user can change a message status multiple times, and the history of these changes is required, then the simple many-to-many relationship of MessageUser will not suffice.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2011
    Posts
    4
    I see there are 2 more columns on MessageUserStatus, one is comments for Accepted and the other is comments for Declined, and yes, only those 2 events generate comments

    You can't perform an action on a message twice, like accepting it one day and again on another

    I'm not sure where you goin with that, altough I'll make a guess:

    "Since event name and date would be the only fields on an Event table and also the primary key of it there is no point on making it?"
    Last edited by pmminov; 05-31-11 at 19:47.

  4. #4
    Join Date
    May 2011
    Posts
    4
    Ok I got what you mean now

    The whole table is the history itself, so every date gets "burn" once and only once

    The fact that only 2 events generate comments is puzzling...

    So whats right, whats wrong?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    It would seem that you could combine the two tables, then.
    If you have data, verify that there is a one-to-one relationship between records in MessageUser and MessageUserStatus.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2011
    Posts
    4
    K, thanks

    So it is correct to have rows with empty fields and then filling them one by one at a given time? Thought that was an annomaly

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    No, there is no problem with having NULLs in some columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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