Looking for suggestions for Table design modification

[sqrob]

I have to make modifications to an existing database and would like to have other opinions prior to working on it.

The old structure for the Member_Type table was:

memID - autonumber (PK)
memType - text (duplicates=no)
cost - monetary (duplicates=OK)

Back then, any type of membership could be added or deleted, but it was thought that the need for it would not come up for another 20 years. Of course this one existing table relates to alot of the queries, forms and reports in the existing database.

Now, 2 years later, the request is to keep track of all membership types, even after they have been dissolved/discontinued and to know how much each individual type cost for a given year to keep track of what everyone should have been paying, way-back-when. They still want to be able to add new ones, as well.

The first and seemingly easiest way was to set up a table as follows:

memID - autonumber (PK)
memTypeA - monetary (duplicates=OK)
memTypeB - monetary (duplicates=OK)
memTypeC - monetary (duplicates=OK)
memTypeD - monetary (duplicates=OK)
memTypeE - monetary (duplicates=OK)
...
year - (duplicates=no)

This works fine, providing that the end-users do not have to add new membership types, so I am trying to figure out if I should have it broken down into 2 or 3 tables and try some form of pivot-table or make-table query from there.

Although it should be straightforward, I get the feeling that I am missing something obvious and can't see the proverbial forest for the trees.

Suggestions, anyone?

Thanks in advance.

 

[Maquis]

Can a single member have more than one type of membership at the same time? If so, then you probably need to split the membership type out to another table and relate it back to your member table with a many-to-one relationship.

If members can only have 1 type of membership at a time then your job may be easier. Just add a BeginDate field to the member table and create a MemberType History table to store the member's previous types. (Perhaps not the most efficient way to store the data, but it save you having to re-design all the forms, queries, etc) Maq
<insert witty signature here>

 

[sqrob]

At any given time, a person may only hold one type of annual membership but it can be changed to another type the next year and the new amount will be charged.

Thanks for your input Maq, it made me realize something that I had overlooked in another area of the database.

This appears to be growing into a bigger project than I was told.


SqRob