Normalization!

[Niklas Östergren]

Hi!

I´m having a little of a problem with the normalization of one table in my
db. It´s a db storing data of people that our organisation are in contact
with. Some of them are members some ar not.

The table I´m having problem with (tblFamilyMember) is going to store
family´s so we know which members livs together (have the same post addrsss)
which will make it easyer to send regular post to the same address one
letterinstead of perhaps 5 letters to the same address.

The db is going to be splitted into a frontend and a backend when I´m
finnished! And Im running Access 2002.

I have the following table structure:

tblPerson.............................tblMemberShip.........................
....tblMember
PersonID (ONE)--(MANY) fkPersonID.................................MemberNo
FirstName.............................fkMemberID (MANY)--(ONE) MemberID
LastName.............................MemberShipID
(AutoNo)...........WelcomeNoteSent
DoB.....................................MemberShipStartDate.................
MemberCardOrderd
Gender.................................MemberShipEndDate..................Me
mberCardSent
....Etc.


No I want to add my table (tblFamilyMember) in which I have following fields
(to start with):
FamilyID (AutoNo)
fkPersonID (Long)
FamilyMemberNo (Long, Going to use MemberNo from tblMember on the person
which is mainmember)
MainMember (Boolean, can only be ONE person / family).

The thing is that I don´t want to look my self in a corner and want therfore
also be able to store family´s generally even if they aren´t members.

I have been thinking of this problem on and off and just can´t deside which
way to go! This is a calssic example of a relational db and I just can´t
deside which solution is the correct one *URG*.

Mu Q´s are:
1.) Where shall tblFamilyMember fit into the relationships of the other
specifyed tables?
2.) What fields would be neccesary (accept the once allready specifyed)?

Any help would be much apreciated!

TIA!
// Nikals

 

[Nikos Yannacopoulos]

Niklas,

I'm not sure I'm following your FamilyMember approach, but I can think of an
alternative you might want to think over. I would add an AddressID field in
the Person table, and add an Addresses table bound one to many to the former
on the AddressID field. That way each address in the Addressses table is
unique, your mailing can use that one, and you don't have to replicate
addresses or bother with family stuff (unless you need it for some other
reason).

HTH,
Nikos

 

[Niklas Östergren]

Well, we also need to know which member belongs to which family since
there´s a differences in memberfeel for family members compared to the rest
of us! So I have several reasons to why I need to know the configuration of
the family´s!

I don´t know if I missunderstod you but I´m not talking about e-mail but
regular mail. Besides I have more tables in this db which I didn´t specifyed
since it doesn´t have anything with my Q to do. Two of thes other tables are
tblPersonAddress on which I store the complete address to a person. And if
several persons have same address I just "block" this possabillity in the
form so a family only have one address stored.

I also have a tblPersonEmailAddress in which I store... right,
e-mailaddresses. No limitations here (yet)!

So I guess my two Q still stands!

Thanks for taking time and comming up with an idéa!


// Niklas