[AccessD] Normalization question

[John Bartow]

IMO - if he wants a learning experience you certainly shouldn't start with a mess and think it will get better. That will most likely convince him that Excel was the way to go.

The table structure is the most important thing. 

Teach him table structure and relationships and in the end he'll appreciate it much more and you'll have a program worth using.

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Susan Harkins
Sent: Thursday, July 19, 2018 7:11 AM
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Subject: [AccessD] Normalization question


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