[AccessD] Normalization question

[James Button]

Me, 
I'd say start with a roughing of the data input form 
Consider what would be the main key (computer generated - sequence or hashing -
date-time and enter'er id)

Then consider what would need to be validated - That's what the form process is
for - a major annoyance in Excel being that data validation can be removed, or
bypassed with the copy action.

So - to my thinking - 
a)Membership code (what the membership card shows)
b)Membership period paid for - start & end
c)Membership person - - normally who paid, but may also be a nominated recipient
of the facility 
- As in someone buying a present annually.
d)Names and addresses of those Associated with the membership 
 
so 
a will have a primary b, and historic b's
a will have a c
b will have a c

so - simplify - 
main data is d with codes to indicate the association as in links to b c and
other d's

as in main table is people and their addresses 
search and update - find name check and optionally change 1 or all 'deemed
current' copies of that address

Yes - people can move, families can move, families can split, families can merge

Different people can pay for a periods membership - and then transfer that to
others for all, or part of the period

Then - who gets mailed re subscriptions 
Then who gets mailed re membership
Who gets mailed re activities - invites to attend, invites to pay.

Point him at the above, and indicate that before starting the setup, what he
should do, (to avoid almost complete reworks) is work out what of the above, and
similar conditions and actions need to be catered for 

Yes - I have been asked to do similar projects and always managed to avoid them
- 
But if you are going to go to Access, then remember views of specific sets of
the bulk data are a great help -
As in select from all people those with a membership code that is currently paid
up, and then get distinct addresses for mailing limitations 

Pretty much doing within the DBMS via view and query what would be done using
VBA and pivottables in Excel

JimB

(Still trying for an effective Teflon coating - even retiring don't work!) 

  

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Charlotte
Foust
Sent: Thursday, July 19, 2018 3:37 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Normalization question

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