[AccessD] Normalization question

[John Colby]

I did something vastly more complicated (data migration) years ago.  An 
entire company doing insurance claim processing from a single huge 
record for each claim.  Policy, claimant, claim etc all in a big record.

I needed to perform a "click and it's done" data migration so that they 
could continue to do their work until such time as I had the migration 
process complete and a small application to actually process all of the 
pieces once the migration happened.

The migration consisted of identifying the entities.  Claimant, claim, 
policy etc.  Then a query to cut that "piece" out of the main record and 
insert into a record with autonumber PK.  Then a field in the main 
record to grab the PKs and insert back in to the main table.  Then cut 
that section of data out.  Rinse and repeat.  Something has to be the 
"parent".  In my case it was a claim.  Insurer was parent to policy.  
Policy was parent to claim.  But that means that claim had a PK from a 
policy which had a PK for insurer.  Claim had a PK from claimant etc.

It took a while to get that whole process of creating the entity tables, 
filling them, getting their PKs back into fields in the records they 
came from etc.

Once I had the tables and could just click a button to do that 
migration, then I had to build forms to edit / add / delete the entities 
- claimants, policies claims etc.  Once the entities could be 
maintained, train the users on how to use those new forms and what the 
pieces were.  Then click a migration button and turn the system on.

It was NOT trivial!  But you get the idea of the strategy I used to do 
the migration and new database build.


On 7/19/2018 8:11 AM, Susan Harkins 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.
>
>

-- 
John W. Colby