one-to-many problem

[Guest]

I have a conference registration database. Members may register or
non-members may register. The one-to-many relationship works for the
non-member registrants but does not work for the member registrants. The
memberid is the primary key for the member table and the nonmemberid is the
primary key for the nonmember table. The registration table contains both
fields. Am I limited to one such relationship or what could be the problem?
Actually, the one-to-many comes up in the dialogue box but it does not allow
me to set referential integrity. Help?

 

[Guest]

It seems to me that there are two ways you can consider this model. Common
to both, however, is that there will be only one foreign key in the
Registration table, which will then allow you to create a single relationship
and enforce referential integrity. The possible models which suggest
themselves to me are:

1. Members and non-members can be regarded as subtypes of a single type,
registrants. Types and subtypes are modelled by means of a one-to-one
relationship, so the primary key of Registrants would be RegistrantID say,
and the keys of Members and Non-members would also be foreign keys
referencing this primary key of Registrants. The relationship with the
Registration table would be from the Registrants table, with a foreign key
RegistrantID in the Registration table referencing the primary key of
Registrants.

2. Member and Non-member can be regarded as a value of a Membership
attribute of a single Registrants table, i.e. there would not be separate
Members and Non-members tables, but a single table with a column representing
the Membership attribute (this could be a Boolean (Yes/No) column, True for
members False for non-members). The relationship would thus be between this
single Registrants table and the Registration table, again on RegistarntID.

The first model would be more appropriate if the members and non-members
entity types have differing attributes, i.e. if there are columns which are
relevant to members but not to non-members, and vice-versa. Shared
attributes such as name, address etc would of course be represented by
columns in the Registrants table, while the attributes specific to the member
and non-member subtypes would be columns in the Members and Non-members
tables.

The second model would be appropriate if members and non-members share a
single set of attributes, i.e. if there are no columns representing attribute
types specific to one or the other.

When considering this remember that its differing or shared attribute
*types* not attribute *values* which are relevant, e.g. members could have an
attribute type Membership Number, an attribute type which is not shared by
non-members of course.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you Ken. I had a hard time finding my original question when I
searched using key word relationships -- posts were not ordered
chronologically as usual?

Anyway, I believe your answer to my question about being limited to one such
relationship is "yes." You suggest 2 models. Only the 1st model is an
option since the members table is downloaded from a national membership
database and contains attributes not applicable to nonmembers. I have never
used subtypes as you suggest but will give it some thought.

Thanks.