Table design help!

[Niklas Östergren]

Hi!

I thought that I had a good table design until I faced a problem which I
have got some help with here. Right now I have stoped developing since I
need to figure out if I´m going to keep the table design and relationships
that I currently have or not. What I need help with is someone to diskuss
this matter with and hopfully it ends up with a decision.

I´m developing a member db for an association, storing data of our members
(names, addresses, phonenumbers, memberships etc.). Right now I´m working
with the registration of membershops, wich is a vital part of this db and
also the most common task for the user to do.

There are three different types of people that the user shall registrate a
new membership on:

1.) New members which isn´t allready in the db
2.) New members which allready is registrated by name, adress etc. but never
have payed the member fee.
3.) Old members which either have a valid membership or don´t have a valid
membership (a member pays the member fee for next period before the last
period of the member fee is invalid).

A new membership can be registrated in two different ways:
1.) If the person allready exist in the db
- The user open up a form showing all registrated people from tabel <
tblPerson > AND < tblNewMemberShipEntry >. The user then select the person
and starts a guid helping the user out with different selections about the
membership.

2.) If the person does NOT exist in the db:
- Here the user have two choises depending on what´s available. Either
to import the data from a textfile (which we get if the person applying for
membership apply via our web- site) or manually entering all the data via a
form into < tblNewMemberShipEntry >. And then start a guide which copy some
of the data from < tblNewMemberShipEntry > into < tblPerson > and then help
the user with some selection reguarding memberships.

I have a primary key in < tblNewMemberShipEntry > [NewMemberShipEntryID]

is [fkNewMemberShipEntryID] and the relatinship is ONE to ONE.

Her´s how I was thinking when I first designed the tables and the
relationship:
- It´s quit common that a person apply for membership but never pay´s the
membership fee. Therefor I wanted to use < tblNewMemberShipEntry > more or
less as a filter between all data comming from outside to the main tabel <
tblPerson >. And also easily, when the user whants to, delete the records in
this tabel < tblNewMemberShipEntry > that have a created date older than
xxx.

So the main reason was to seperate the records which never ends up in a
valid membership.

Now I have realised that this is causing me some problems and I get
redundance in my db. So I need some help with how you see on this problem
and what you would have done.

I´m still in the beginning of developing fase so it´s not any major work
neccesary to change the table design if I need to. That´s why I ask now!

TIA!
// Niklas

 

[Dirk Goldgar]

Hi!

I thought that I had a good table design until I faced a problem
which I have got some help with here. Right now I have stoped
developing since I need to figure out if I´m going to keep the table
design and relationships that I currently have or not. What I need
help with is someone to diskuss this matter with and hopfully it ends
up with a decision.

I´m developing a member db for an association, storing data of our
members (names, addresses, phonenumbers, memberships etc.). Right now
I´m working with the registration of membershops, wich is a vital
part of this db and also the most common task for the user to do.

There are three different types of people that the user shall
registrate a new membership on:

1.) New members which isn´t allready in the db
2.) New members which allready is registrated by name, adress etc.
but never have payed the member fee.
3.) Old members which either have a valid membership or don´t have a
valid membership (a member pays the member fee for next period before
the last period of the member fee is invalid).

A new membership can be registrated in two different ways:
1.) If the person allready exist in the db
- The user open up a form showing all registrated people from
tabel < tblPerson > AND < tblNewMemberShipEntry >. The user then
select the person and starts a guid helping the user out with
different selections about the membership.

2.) If the person does NOT exist in the db:
- Here the user have two choises depending on what´s available.
Either to import the data from a textfile (which we get if the person
applying for membership apply via our web- site) or manually entering
all the data via a form into < tblNewMemberShipEntry >. And then
start a guide which copy some of the data from <
tblNewMemberShipEntry > into < tblPerson > and then help the user
with some selection reguarding memberships.

I have a primary key in < tblNewMemberShipEntry >
[NewMemberShipEntryID] which I copy into < tblPerson > so I know from
where the data have been copyed and so I don´t copy it twice. The
corresponding field in < tblPerson

is [fkNewMemberShipEntryID] and the relatinship is ONE to ONE.

Her´s how I was thinking when I first designed the tables and the
relationship:
- It´s quit common that a person apply for membership but never pay´s
the membership fee. Therefor I wanted to use < tblNewMemberShipEntry

more or less as a filter between all data comming from outside to

the main tabel < tblPerson >. And also easily, when the user whants
to, delete the records in this tabel < tblNewMemberShipEntry > that
have a created date older than xxx.

So the main reason was to seperate the records which never ends up in
a valid membership.

Now I have realised that this is causing me some problems and I get
redundance in my db. So I need some help with how you see on this
problem and what you would have done.

I´m still in the beginning of developing fase so it´s not any major
work neccesary to change the table design if I need to. That´s why I
ask now!

Niklas -

Probably the group <microsoft.public.access.tablesdbdesign> would have
been a better place to post. I can't afford to spend a lot of time
thinking about your design at the moment, and I haven't built a
membership application quite like your before, but here are my immediate
thoughts on the matter.

I don't see a lot of point in having two separate tables for personal
information. I think I would have just one table for members,
prospective members, and ex-members. I would also have a related table
for membership payments. This latter table would include the PersonID,
the amount and date of payment, and the start date and/or end date of
the membership period the payment covers. Maybe this table would be
called tblMemberships.

So: records in tblPerson that have no matching records in
tblMemberships have never been members, so they are prospects. Records
in tblPersons whose maximum membership-end-date (in tblMemberships)
precedes the current date are ex-members. Records in tblPersons for
which a record exists in tblMemberships where the current date is
between membership-start-date and membership-end-date are current
members.

If you want to, you can periodically remove "prospects" -- as defined
above -- that were created longer ago than some specified period. Or
you can just filter them out of forms and reports. You can also
eventually remove old members if their latest membership is older than
some period -- or flag them for a phone call or mailing.

 

[Niklas Östergren]

Thanks for your reply Dirk!

I appreciate you taking time to dig into my problem.

I´m sorry for posting this Q at the wrong place. I didn´t even know ther was
a forum just for table design. But now I do, tanks!

The only thing that I wasn´t quit clear of was just this second table
holding almost same data as tblPerson. The rest of the table design is
allready there and hopfully working good. And as you mentioned I DO have a
table holding memberships with ONE - MANY relation between tblPerson and
tblMemberShips.

I have thought of this during the day and I have come to the conclution that
I should have only one tabel. I havn´t started to change the tabel design
yet but I will. And you have helped me come to a desition.

Middle tabel into which I import data from the textfiles. I do´n´t know if
this is the regular way to do when importing data. But it semas as a good
idé. Imideatly after the user have answered a Q if he/she want to registrate
the person just imported I copy the data to tblPerson, tblPersonAdress,
tblPersonPhone etc.. And when this have been done successfully I delete the
record in the middle tabel.

Thanks again Dirk, now I´ll sleep nice this evning knowning that I can
continue working with a table design that I beleve in.

// Niklas

Hi!

I thought that I had a good table design until I faced a problem
which I have got some help with here. Right now I have stoped
developing since I need to figure out if I´m going to keep the table
design and relationships that I currently have or not. What I need
help with is someone to diskuss this matter with and hopfully it ends
up with a decision.

I´m developing a member db for an association, storing data of our
members (names, addresses, phonenumbers, memberships etc.). Right now
I´m working with the registration of membershops, wich is a vital
part of this db and also the most common task for the user to do.

There are three different types of people that the user shall
registrate a new membership on:

1.) New members which isn´t allready in the db
2.) New members which allready is registrated by name, adress etc.
but never have payed the member fee.
3.) Old members which either have a valid membership or don´t have a
valid membership (a member pays the member fee for next period before
the last period of the member fee is invalid).

A new membership can be registrated in two different ways:
1.) If the person allready exist in the db
- The user open up a form showing all registrated people from
tabel < tblPerson > AND < tblNewMemberShipEntry >. The user then
select the person and starts a guid helping the user out with
different selections about the membership.

2.) If the person does NOT exist in the db:
- Here the user have two choises depending on what´s available.
Either to import the data from a textfile (which we get if the person
applying for membership apply via our web- site) or manually entering
all the data via a form into < tblNewMemberShipEntry >. And then
start a guide which copy some of the data from <
tblNewMemberShipEntry > into < tblPerson > and then help the user
with some selection reguarding memberships.

I have a primary key in < tblNewMemberShipEntry >
[NewMemberShipEntryID] which I copy into < tblPerson > so I know from
where the data have been copyed and so I don´t copy it twice. The
corresponding field in < tblPerson

is [fkNewMemberShipEntryID] and the relatinship is ONE to ONE.

Her´s how I was thinking when I first designed the tables and the
relationship:
- It´s quit common that a person apply for membership but never pay´s
the membership fee. Therefor I wanted to use < tblNewMemberShipEntry

more or less as a filter between all data comming from outside to

the main tabel < tblPerson >. And also easily, when the user whants
to, delete the records in this tabel < tblNewMemberShipEntry > that
have a created date older than xxx.

So the main reason was to seperate the records which never ends up in
a valid membership.

Now I have realised that this is causing me some problems and I get
redundance in my db. So I need some help with how you see on this
problem and what you would have done.

I´m still in the beginning of developing fase so it´s not any major
work neccesary to change the table design if I need to. That´s why I
ask now!

Niklas -

Probably the group <microsoft.public.access.tablesdbdesign> would have
been a better place to post. I can't afford to spend a lot of time
thinking about your design at the moment, and I haven't built a
membership application quite like your before, but here are my immediate
thoughts on the matter.

I don't see a lot of point in having two separate tables for personal
information. I think I would have just one table for members,
prospective members, and ex-members. I would also have a related table
for membership payments. This latter table would include the PersonID,
the amount and date of payment, and the start date and/or end date of
the membership period the payment covers. Maybe this table would be
called tblMemberships.

So: records in tblPerson that have no matching records in
tblMemberships have never been members, so they are prospects. Records
in tblPersons whose maximum membership-end-date (in tblMemberships)
precedes the current date are ex-members. Records in tblPersons for
which a record exists in tblMemberships where the current date is
between membership-start-date and membership-end-date are current
members.

If you want to, you can periodically remove "prospects" -- as defined
above -- that were created longer ago than some specified period. Or
you can just filter them out of forms and reports. You can also
eventually remove old members if their latest membership is older than
some period -- or flag them for a phone call or mailing.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)