Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Members with Different Detail tables

    Hi all.

    I've got a strange issue I've never ran into before, and I'm wondering what the best design method might be for this problem. The one I've come up with below feels flawed using a flagging system:

    I've got MEMBER's. Each MEMBER can have a different level of detail associated to their account. To try and keep this different information organized, this is my proposed solution:

    MEMBER
    ----------
    id (pk)


    MEMBER_MEMBER_DETAIL_REL
    ----------
    member_ID (fk)
    member_detail_ID (fk) // id of one of the tables below
    member_detail_type_flag (char) // This tells you which table to look at.


    MEMBER_DETAIL_REGULAR
    ----------
    id (pk)
    // extra member details here


    MEMBER_DETAIL_UNPAID
    ----------
    id (pk)
    // extra member details here


    MEMBER_DETAIL_AFFILIATE
    ----------
    id (pk)
    // extra member details here


    MEMBER_DETAIL_SPONSOR
    ----------
    id (pk)
    // extra member details here

  2. #2
    Join Date
    Jun 2009
    Posts
    2

    My other idea was...

    My other idea was to simply link each table to the MEMBER TABLE directly, and put the flag in the MEMBER table that tell me which child table to look at for the details. Maybe that's a better solution?!?!?

    Anyway, I look forward to any replies. Thanks.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the flag in the members table which tells you which detail table to look in...

    ... implies two SELECTs, one for the member, then an "if" statement in your application language which controls which of the details tables you go after next

    better would be, if there aren't ~too~ many different details tables, simply to LEFT OUTER JOIN to all of them

    that's just one query instead of two

    for further information, search supertype/subtype

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

Posting Permissions

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