Something like this:
Party
id
type: {individual, group}
name
PartyRelationship
fromPartyId
toPartyId
type: {groupMembership}
Event
id
name
scheduledStart
scheduledEnd
actualStart
actualEnd
EventRole
eventId
roleType {organizer, scheduler, participant}
partyId
List
id
eventId
name
ListItem
id
listId
description
createdBy /* should point to a party */
An Event hasMany Roles, which could be organizers, participants, etc.
A participant can be either an individual or group, both of which are subtypes of Party
A Group party has many Individual parties, as described in the PartyRelationship table. An Individual can belong to zero+ groups.
An Event hasMany Lists, and a List hasMany Items. A list belongsTo one Event, and a ListItem belongsTo one List.
Example usage:
insert into party values (1, 'individual', 'neil');
insert into party values (2, 'individual', 'bernk');
insert into party values (3, 'individual', 'simon');
insert into party values (4, 'individual', 'ypercube');
insert into party values (5, 'group', 'canadians');
insert into partyRelationships values (5, 1);
insert into partyRelationships values (5, 2);
/* now neil and bernk are related to the 'canadians' party */
insert into event values (1, 'an event with canadians and ypercube, organized by simon', '2012-07-01', '2012-07-02');
insert into eventRoles values (1, 'organizer', 3); /* simon is the organizer*/
insert into eventRoles values (1, 'participant', 5); /* the 'canadians' group is a participant */
insert into eventRoles values (1, 'participant', 4) /* ypercube is a participant too */
I normally go with modified version of (2).
payment_type (payment_type_id, PK(payment_type_id) );
payments(payment_id , payment_type_id, [other_attrubutes],
PK(payment_id), UNIQUE(payment_id,payment_type_id), FK(payment_type_id)
);
paypal_payment(payment_id , payment_type_id, [paypal_attributes],
PK(payment_id), FK(payment_id ,
payment_type_id) REFERENCES payments(payment_type_id,payment_type),
CHECK (payment_type_id='paypal')
)
manual_payment(payment_id , payment_type, [manual_attributes],
PK(payment_id), FK(payment_id ,
payment_type_id) REFERENCES payments(payment_type_id,payment_type),
CHECK (payment_type_id='manual')
)
Check constraints on each detail table ensures that manual table details never go into paypal table, and vice versa. Unique constraint allows detail tables to have a reference to 2 columns.
(3) is also not so bad, but I don't like exclusive arcs - adding new payment type will require changes to payments, and trigger used to enforce that only one of columns is not null.
Best Answer
You could programmatically build queries to test which combinations of columns look like they might be keys. In order to test a given relationship you'd run a query of the form:
This can give you a fairly raw list of key relationship candidates. I would write a SQL query to programmatically generate all possible queries of the above form, paste all of them into a new SSMS window and run time.
You probably should exclude candidates with zero non-NULL values in the child table column.
Another problem is that bad data might have slipped in. Heck, since there are no FKs it almost certainly has slipped in somewhere.
Also, this only finds single-column combinations.