Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Unanswered: Need Help Figuring out how many tables to create

    Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:

    - Week Number (the current week number in the year)
    - User ID
    - Events
    - Meetings
    - Activities
    - Accomplishments

    And each user would have one row in the database per week.

    OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain

    - eventID (auto incremented number)
    - userID
    - weekNumber
    - event_description

    Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.

    Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.

    Thanks, Dave

  2. #2
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    I'd say you should only have two tables with the two sets of columns you've listed. You're not likely to have that many events that you're objects are going to be huge are they? Fewer objects will make it a little easier to manage.

    Peace,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should use only one table to record your events, meetings, etc:

    - ItemID (auto incremented number)
    - userID
    - ItemType ('Event', 'Meeting', 'Activity', 'Accomplishment')
    - weekNumber
    - Item_description
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    However if you use one table, it won't be normalized. You will be keeping redundant information if you do that...

    -kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  5. #5
    Join Date
    Apr 2006
    Posts
    4
    Kilka - I'm leaning towards not using one table because of the exact reason you stated. I would like the table(s) to be normalized so that performance is not affected. Can you please explain what you mean that I "should only have two tables with the two sets of columns"? Please clarify what you mean. I was thinking that I could create a table for each category (events, meetings, activities, accomplishments) and then just create a view that would combine the tables somehow so that information can be queried faster. What do you think?

  6. #6
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    On table with
    - Week Number (the current week number in the year)
    - User ID
    - Events
    - Meetings
    - Activities
    - Accomplishments

    Another with
    - eventID (auto incremented number)
    - userID
    - weekNumber
    - event_description

    And then you can put a view to join them over userID.

    Cheers,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  7. #7
    Join Date
    Apr 2006
    Posts
    4
    I don't follow. Those two tables seem redundant to me. Can you explain your concept? Thanks.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Kilka101
    However if you use one table, it won't be normalized. You will be keeping redundant information if you do that...

    -kilka
    Wrong. The schema I suggested is fully normalized.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    you would want to have something like

    UserID,
    Password,
    ProfileInfo

    in one table,

    and in the other table, you put the information that would be redundant per user.
    - eventID (auto incremented number)
    - userID
    - weekNumber
    - Events
    - event_description
    - Meetings
    - Activities
    - Accomplishments

    Cheers,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  10. #10
    Join Date
    Apr 2006
    Posts
    4
    blindman - I like your concept.

    ItemID (auto incremented number)
    userID
    ItemType ('Event', 'Meeting', 'Activity', 'Accomplishment') - foreign key
    weekNumber
    Item_description

    I guess I should also have another table called 'itemtypes' that would have the following columns:

    typeID (auto incremented number) - primary key
    typeName ('Event', 'Meeting', 'Activity', 'Accomplishment')

    And then the first table will just contain the typeID. How's that sound?

  11. #11
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    Opps. on second look, you're right blindman. I had assumed you were trying to create a profile for each user as well dave, and join that profile information on event information.

    peace,
    -kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dave914
    blindman - I like your concept.

    ItemID (auto incremented number)
    userID
    ItemType ('Event', 'Meeting', 'Activity', 'Accomplishment') - foreign key
    weekNumber
    Item_description

    I guess I should also have another table called 'itemtypes' that would have the following columns:

    typeID (auto incremented number) - primary key
    typeName ('Event', 'Meeting', 'Activity', 'Accomplishment')

    And then the first table will just contain the typeID. How's that sound?
    It's not a bad idea to have such a lookup table to enforce business rules through relational integrity.
    Putting all these different types of "Items" in a single table allows you to write code across all of them simultaneously. "Show me everything scheduled during a certain time-span, regardless of what the activity was."
    There may come a time when you want to separate out the different Item types in distinct columns for display purposes, and you can construct a cross-tab query to do this either in SQL Server or in your presentation layer.
    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
  •