[AccessD] Normalization question

[Susan Harkins]

It shouldn't be a problem with the layout we settled on -- membership is the
parent and the only child table with be the many to many with the membership
"people." There are two lookup tables -- turning out to be very simple. 

Susan H. 


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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com