[Niels]

Hello,

I've got a designproblem. Here it is.

I've got a table with persons called t_Person. These persons can be part of a group. A group has an owner, which is a person from the t_person table. So I've created a table called t_group which has the following structure.

ID
Owner_ID
Person_ID

Owner_ID is an ID from t_Person and is the owner of the group. The Person_ID holds one of the members of this group. I use the t_group table because a person can be part of multiple groups.

So far, so good.

Now comes my problem. Persons as well as groups can have one or more memberships.
My questions is how do I setup this membership table, for a membership could be owned by a group or a person. Should this table have to fields, Person_ID and Group_ID of which one gets filled?

Please some ideas.

Thanks Niels

[andrewst]

First of all, your t_group table definition seems to be incorrect. It only allows a group to have 1 person in it! The 1:m relationship between Group and Person should be defined like this:

CREATE TABLE Group
( Group_ID ... PRIMARY KEY
, Owner_ID REFERENCES Person
, ...
);

CREATE TABLE Person
( Person_ID ... PRIMARY KEY
, Group_ID REFERENCES Group
, ...
);

Or if a person can belong to more than one Group you will need an intersection table e.g. Person_Group

(Aside: why put "t_" prefixes on tables? Just makes SQL harder to read).

Now, regarding memberships, you have an "arc" relationship: a Membership is owned by either a Person or a Group.
One way to implement this is as you describe, with the addition of a check constraint to enforce the arc:

CREATE TABLE Membership
( Membership_ID ... PRIMARY KEY
, Person_ID REFERENCES Person
, Group_ID REFERENCES Group
, ...
, CONSTRAINT Mem_arc CHECK ( (Person_ID IS NULL AND Group_ID IS NOT NULL) OR (Person_ID IS NOT NULL AND Group_ID IS NULL) )
);

Alternatively, you could create 2 table Person_Membership and Group_Membership.

If there are some common attributes (other than Membership_ID) then you could go for a 3-table approach:

CREATE TABLE Membership
( Membership_ID ... PRIMARY KEY
, Membership_Type VARCHAR2(1) NOT NULL CHECK (Membership_Type IN ('P','G'))
, ... -- common columns
, CONSTRAINT Membership_UK UNIQUE (Membership_ID, Membership_Type)
);

CREATE TABLE Person_Membership
( Membership_ID ... PRIMARY KEY
, Membership_Type VARCHAR2(1) NOT NULL CHECK (Membership_Type ='P')
, Person_ID REFERENCES Person
, CONSTRAINT ... FOREIGN KEY (Membership_ID, Membership_Type) REFERENCES Membership
);

CREATE TABLE Group_Membership
( Membership_ID ... PRIMARY KEY
, Membership_Type VARCHAR2(1) NOT NULL CHECK (Membership_Type ='G')
, Group_ID REFERENCES Group
, CONSTRAINT ... FOREIGN KEY (Membership_ID, Membership_Type) REFERENCES Membership
);

You could create views to join the subtype tables to the supertype for convenience.

Choose whichever approach seems most suited to your needs - obviously, the first approach (your idea) is the simplest to implement.

[nalla]

Hello,

Sometimes the ambiguities you are facing can be resolved by starting your analysis with use cases. I have given below an example. See whether it resolves your abiguity.

==============================================
Write uses cases:

1. user -> ( join group )
2. admin -> ( create group )
3. admin -> ( assign owner to group )

==============================================
Identify subsystems: << by grouping related use cases >>

UserAPP (1)
GroupApp (2, 3)

==============================================
Identify services that each app needs:

UserApp
- createPerson()
- getGroups()
- getGroupDetails()
- createMember() << assign person to group >>

GroupApp
- createGroup()
- getMembers()
- getMemberDetails()
- setGroupOwner()

Note: The services are typically identified by writing event flows for each use case.
See http://www.theserverside.com/pattern...hread_id=17595 for an example.

==============================================
Identify components: << by grouping related services >>

PersonMgr
- createPerson()

GroupMgr
- getGroups()
- getGroupDetails()
- getGroupMembers()
- setGroupOwner()

MemberMgr
- createMember()
- getMembers()
- getMemberDetails()

==============================================
Identify database tables: << on a per-component basis >>

1. The PersonMgr needs a table like:
PERSON [ person_id, person_details ]

2. The GroupMgr component needs tables like:
GROUP [ group_id, group_desc, owner_member_id ]
PERSON_GROUP [ member_id, person_id, group_id ]

3. The MemberMgr component needs a table like:
PERSON_GROUP [ member_id, person_id, group_id ]

==============================================

Nalla

[AdemMeral]

In my apps i use the member and group in the same table , and use a IsGroup bit field. Since groups can be assigned user rights and details it works great. Membership is in one table.


The user/group table is like
intRecRef
intIsGroup
strDescr

....

The membership is like
intRecRef
intGroupUserRecRefMember
intGroupUserRecRefGroup


just my $.02