Design and Relationships

[cb]

Hi,

I'm in charge of a membership database and am using Access
2000. I'm having trouble with the initial setup and the
relationships. I created 4 tables and tied them together
using the MemberID field. The member ID field is a
numeric field and is not autonumber. I inherited this
database and need to use established MemberIDs. I do have
an ID field for each table that is the primary key.

FirstTable: Member information such as MemberID , name,
address, phone number, corporate (y/n), etc.

SecondTable: Membership information - MemberID,
expiration date, renewal date, number of years renewed,
etc.

ThirdTable: Information specific to individual members
(non corporate), MemberID, DOB, Board Member, etc.

FourthTable: Information specific to corporate members
MemberID, product, service, slogan, etc.

Any ideas/suggestions on the initial setup? I want to do
this right from the beginning, so that it will be easier
to expand in the future.

Thanks.

cb

 

[Rose]

As long as your Member ID field in your first table is set
up to be the primary key (because it will never duplicate),
you're golden.
If the Member ID might repeat in your other tables, use an
autonumber field for your primary. If they won't repeat,
and you're just setting up four separate tables with
one-to-one relationships to make the data more manageable
than what it would be all in 1 table, you can use Member ID
for the primary key in all of your tables.
2 major rules for setting up tables are:
1) Take a look at your tables. How much information is
being repeated in multiple records? Repetition is
frequently a sign that information should go in a separate
table. For instance, if you have a member, and they have a
salesperson, you wouldn't want the salesperson's name,
phone #, etc all repeating in your *member* table. The
primary key should be the only thing that repeats in
another table.
2) Do you have either one-to-one or one-to-many
relationships? One-to-ones mean that you *could* combine
your data into 1 table if you wanted to - it'd save you
some trouble, but might look unwieldy. If they're
one-to-many, you're good. Just be on the lookout for
many-to-manys, because that's when you've got a problem.

 

[Tim Ferguson]

FirstTable: Member information such as MemberID , name,
address, phone number, corporate (y/n), etc.

SecondTable: Membership information - MemberID,
expiration date, renewal date, number of years renewed,
etc.

It's not clear from this how Memberships are different from Members. Can a
person/ corporation have more than one membership current? Or do you keep
track of historic memberships? If every Member has exactly one Membership,
then you may as well keep these fields in the first table. If a Member can
have more than one Membership record, then you need to specify the full PK
of this table: presumably (MemberID, ExpirationDate) or something.

ThirdTable: Information specific to individual members
(non corporate), MemberID, DOB, Board Member, etc.

FourthTable: Information specific to corporate members
MemberID, product, service, slogan, etc.

Yes: these tables implement what is known as Subtyping, with the Members
table being the Supertype and each of these being subtypes. Note that the
field Members.Corporate is redundant, since you can tell which members are
corporate or otherwise by the presence of a Corporates record. What will
you do if the field is True and there is a Individuals record? A
disadvantaeg of this approach is that there is no way in Access of
_guaranteeing_ that there will be exactly one Corporates OR one Individuals
record.

Hope that helps


Tim F