[AccessD] Normalization question

[Susan Harkins]

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