[AccessD] Normalization question

[Susan Harkins]

Bob made an interesting point -- he said to think about how they use the db. The only thing they use this db for is to track the memberships so we can look them in when someone arrives without their card. That's it. No analyzing, no mailings, nothing. That's why the Excel sheet was adequate -- it was easy and find feature worked great most of the time. Brian could dump the entire thing into one table and it would still work for them. 

But, for the sake of normalizing and learning -- that's Brian's main motivation -- I have a question and bear with me, I just don't do this anymore and I've forgotten more than I actually learned at this stage. 😊 

Charlotte, I agree with you. Memberships is the parent table, not the member(s). But, if I break out those names into a second table and use an associate to relate them, what happens when we have two John Smiths? Will it even matter? 

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