[AccessD] Normalization question

[Bill Benson]

People change addresses without changing anything else about their
membership. You need to design for that eventuality.

On Thu, Jul 19, 2018, 3:23 PM Susan Harkins <ssharkins at gmail.com> wrote:

> Well, that's a bit of brilliancy tagging the address to the actual
> membership!
>
> Susan H.
>
>
>
> Start with the membership itself as a unique entity with a membership
> number, beginning date and expiration, type of membership, and address.  If
> you want unique members, it would be a many to many with membership.  If
> you
> plan to use the database to do a mail merge or mailings, you want the name
> split into at least first and last, and have a single record for each name.
>
> Charlotte Foust
> (916) 206-4336
>
> On Thu, Jul 19, 2018 at 5:11 AM, Susan Harkins <ssharkins at gmail.com>
> wrote:
>
> >
> > 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.
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>