Subtype / SuperType Relationships: How to fulfill these requirements

Question

I am fighting with myself trying to model a DB to fulfill certain requirements.

• There are two main entities. One is known as Team, think of a business or organization. The other will be an individual.
• Teams can also be made of individuals (TeamContacts)
• This is a member required site (i.e. must become a Member and log into the site to do anything)
• TeamContacts will only be an internal directory and they will not have access to the site unless of course they are a Member and then will have such privileges.
• Individuals classified as TeamContacts may or may not be Members of the site. This is my major concern here. It will be very difficult, at times impossible, to worry if a TeamContact is also a Member and vice versa. Memberships can lapse and TeamContacts may go from team to team. History for Members will be maintained but history to track the possible movement of TeamContacts between Teams will not.
• The only attributes that would be shared exclusively between a Member and a TeamContact (baring the attributes that exist at the Party level) is FirstName and LastName.

Here are four possibilities that I have come up with. As I said I don't really know in what direction I should go. It might be that none of my options are ideal. I would appreciate any feedback that can help sort this out.

Thanks

Answer 1

At a glance, it looks like Ex1 is the only diagram that expresses these two requirements.

• Every team contact is a person.
• Some persons are members.

Ex2 doesn't refer to members at all.

Ex3 would seem to allow any party (including teams) as a team contact.

Ex4 explicitly allows any party (including teams) to be a team contact.

You might consider another unexpressed but common requirement--that a person can be a contact of only one team at a time. (That might not apply in your particular case. I can't tell.)