[AccessD] Normalization question

[Susan Harkins]

One of the new employees at Salato wants to build an Access database for the
membership. I advised him not to because the other employees will refuse to
use it and because the system we're using (Excel sheet) isn't broken, but I
think he's decided it will be a great learning experience, so I'm trying to
help. His first mistake was to allow the Access (365, 2016) wizard to create
tables from the Excel sheet. Not a complete mess, but it created several
lookup fields that even I can't decipher. I don't want to spend my time that
way -- a lot of xyz identifiers and crap. It must be something new because
I've not see the wizard do something like that, but then again, I didn't use
it, so... 

The records are currently in an Excel sheet -- one record with all the data
for each membership. Names are in one cell, even when it's a family
membership and there are two names. (No, I didn't create this, but I made it
work because it's what they were all used to using and frankly, it worked
for their purposes and it was easy.) 

We have five years of memberships we want to access -- but only the current
year is active. We do want the old records for historical value. It might be
easier to work with only the active records and just keep the old Excel
sheet, in case we need to look something up. 

Yesterday, I suggested that he dump the wizard's tables and start over from
scratch -- it is the best way to learn and he agreed. Right away I ran into
two problems. The first is a normalization question. 

Members renew once a year, so the same family might have several memberships
listed, but only one is active. We want to retain that historical
information. There are three types of memberships: Individual, Family, and
Friends. So, a "member" can have many memberships, but a membership can have
one or two members tied to it. At this point, it will never be more than two
because we don't list kids and the additional friends can be anybody. (Of
course, that could change, but I don't see it happening anytime soon.) Each
membership has one or two names tied to it. At first, I thought of the
members as being the parent, but I'm not sure that's the way to go now.
Perhaps the membership itself is the parent. I think we have a many to many
relationship here perhaps. 

So John Doe might have an individual membership and he might have an active
membership and two expired records in the database. 
Sue and George Donaldson might have a family membership with one active and
several records in the database. 
Lonnie and Alice might have a family membership with no active record but
several expired records. 
Bill and Marth might have a friends membership with one active and no
expired records. 

This exposed itself rather quickly because having two first and last names
for each record seems to break normalization rules, but on the other hand, I
can see doing it that way despite the repeated address and phone number
information.

The second problem is of course that there are no primary/foreign key values
in the Excel sheet. So, once Brian creates the tables, he has to find a way
to create those values and keep them straight. So, it's possible that
rewiring the wizard's tables might actually be the best way to go because I
think... I think the wizard created those. But at this point, they have no
value because each record points to only one membership because that's how
it was in Excel. I think it might be a job for Power Pivot before Access but
I haven't gotten that far. We can run insert queries of course, but the more
I consider it, the more I'm inclined to work with only the active
memberships in the db and move forward. The old info will still be there in
an Excel file if it's needed. 

Right now, the db isn't necessary so there's no pressure. Brian just wants
to do it. 

Susan H.