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

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.

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