Help with table design!

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!

I´m in the designfase and havn´t started doing any coding or any developing
of forms and reports yet. Exept for some prototypes for showing the end
user. I´m still collecting data!

I have started designing the tables though and thought, untill this morning,
that I hade a fairly clear thought of how it should look like! But I´m
starting to hesitate a little bit since I don´t quit get a main form to work
properly (testing).

The database is going to store data about members i an association, mainly!

This is my design (3 of totaly 25 tables):

==========================================
tblPerson.................tblMemberValidation.........tblMember
PersonID (1) - (M) fkPersonID
Name......................fkMemberID (M) ---- (1) MemberID
LastName............... MemberShipEndDate........MemberNo
Street......................MemberFee
(etc.).......................(etc.).................................(etc.)

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

This is the way I´m thinking (please correct me if I´m wrong):

ONE (1) person (tblPerson) can have MANY (M) membership in the association
(tblMemberValidation). But the member can only have ONE membernumber
(tblMember). And he/she don´t become a member (get a member number) until
he/she have payed for a mebership (tblMemberValidation).

The reason to why I don´t svae "member number" in tblPerson is that we store
a lot of other peopels data even if they aren´t a member. We´d like to have
name and address to people who have helped us on an event for instance. And
they don´t need to bee a member and therfore don´t need any member number!

I´m I right or should I change something?

TIA
// Niklas
 
The reason to why I don´t svae "member number" in tblPerson is that we store
a lot of other peopels data even if they aren´t a member. We´d like to have
name and address to people who have helped us on an event for instance. And
they don´t need to bee a member and therfore don´t need any member number!

I´m I right or should I change something?
Click to expand...

You could have a MemberNumber field in the People table and just leave
it NULL until they become a member. Your MemberValidation table could
be joined to People by MemberNumber - a NULL foreign key is allowed,
and it simply wouldn't match nonmembers.
 
Hmm, ok!

I´ll think of it but maby we need to store some other data about just
members, which arn´t related to each person, in the future. But it obviously
would simplify the table structure!


Thank´s for the answere
// Niklas
 
I´ll think of it but maby we need to store some other data about just
members, which arn´t related to each person, in the future. But it obviously
would simplify the table structure!
Click to expand...

You can take a small step back toward complexity by "Subclassing" -
creating a Members table in a one to one relationship with the People
table. It would contain fields which pertain only to members, and
would be in a one-to-one relationship with People.

Access is pretty efficient at handling NULL fields, though, so there
would not be a large cost to just including the members-only fields in
the People table and just leaving them null for nonmembers.
 
Hi John!

OK, you have conviced me! I´ll move the data in tblmember in to tblPerson!

Thanks for your help!

// Niklas
 
Back
Top