[AccessD] Normalization question

[Charlotte Foust]

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
>