Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Lightbulb Unanswered: E-mail group database design

    I was just wondering how do a email engines database looks like:

    might be something like this:

    *************
    USER_INFO

    userID(primary key)
    Pwd
    first_name
    Last_name
    date_of_birth
    sex
    add1
    add2
    city
    state
    country
    Zip code
    secret_question
    secret_answer
    ******************

    Email_Record

    receiver_ID(foreign key to userID in USERINFO table)
    sender_ID(foreign key to userID in USERINFO table)
    date
    time
    contents
    attachments

    *******************
    Groups

    UserID(foreign key to userID in USERINFO table)
    group_name
    group_No(primary key)

    *******************
    Group_Members

    Group_No(foreign key to Group_no in Groups table)
    UserID1
    UserID2
    UserID3
    UserID4
    ******************



    This design has lot many limitations like a single group can have only 4 members

    Can anyone suggest a better design..??????

  2. #2
    Join Date
    Jun 2008
    Posts
    3
    My major consideration in this design is group managment.....so I ignored the email part a lil bit...

  3. #3
    Join Date
    May 2008
    Posts
    5

    Imho

    The proper design is:

    Groups

    Group_No(primary key)
    Group_name


    Group_Members

    Group_No(foreign key to Group_no in Groups table)
    UserID(foreign key to userID in USERINFO table)


    you can also add eg. multilevel subgroups if you extend the Groups definition


    regards


    Z. Ch.

  4. #4
    Join Date
    Jun 2008
    Posts
    3

    design

    Hi...
    Thanx 4 the reply

    gud thing abt this design is that there is no limit to number of group members.

    But got some disadvs too..like how 2 keep track of the group owner
    and the size of groupmember table 'll grow enormously...

    what more options do we have...

    thanx
    Learninglife

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Zch has given you the correct design model. If you want more options, perhaps a spreadsheet application or sticky-notes would work for you?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I love the fact you used the term
    Quote Originally Posted by blindman
    spreadsheet application
    Oh the oxymoron
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've journeyed through Excel Hell many times over the course of my career. I would not describe the horrors I have seen, as there are young and impressionable readers of this forum who might not have the stomach for it.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    on the other hand, excel is great for productivity, such as creating column totals, sideways totals, and especially taking one of these total formulae and using Ctrl+R to replicate it sideways and Ctrl+D downwards

    excel can be bloody useful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Occasionally, but more often than not it is a pain in the...
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely useful. But it is not a database.

    I have even developed several Excel Applications. But they were not database applications.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    (note to self) Rudy knows Excel...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    envy is such a shameful emotion

    but hey, if you ever need help with excel, i'd still be willing to help you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2008
    Posts
    5
    But got some disadvs too..like how 2 keep track of the group owner
    and the size of groupmember table 'll grow enormously...
    -------------------------------------------------------------------
    1.)

    Groups

    Group_No(primary key)
    Group_name
    [group owner](foreign key to userID in USERINFO table)

    2.) set ON DELETE CASCADE in the groups' UserID FOREIGN KEY constraints

  14. #14
    Join Date
    May 2008
    Posts
    5
    Sorry
    It should be:

    2. set ON DELETE CASCADE in the Group_Members' UserID FOREIGN KEY constraints

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by learninglife
    But got some disadvs too..... the size of groupmember table 'll grow enormously...
    The table will store the data you require and no more. If you need to store an enormous amount of data then the table will be enormous. Remember though that you are measuring size by depth only (number of rows). Your design is wide (lots of columns). In addition, for those groups with 3 or less members there is an overhead for the NULL values so your design actual takes up unnecssary space on a data page.

    These are all physical concerns. From a logical standpoint this is pretty 101 stuff. Have a google for normalisation and in particular first normal form.

Posting Permissions

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