Building a database backwards

[meierrain]

I want to turn one large, un-normalized table into a relational database.

(Apologies if this is a simple problem. It is one I have not encountered before in my relatively limited work with Access).

I have inherited a “database” that is not a database. It is one table of more than 3 million records with about 50 fields. There is no normalization. It was created from a data-dump of one year’s worth of transaction information for purchases by about 100 different franchise locations.

I cannot access the data in the original system – believe me, I have asked. These data dumps will be occurring every six months from here on out, so I would like to transform this into a relational database that can be updated with each new set of purchase data instead of a series of unrelated tables for different time periods that are queried separately.

I have built smaller databases from scratch before, but I have not transformed existing records in one Access table into many tables. I can conceive of what the different entities would be in this new table – franchises, vendors, the associated addresses, orders, etc. – and what kind of query would yield the complete records I have now, but I do not see how to reassemble all the original records once I have broken down the fields into separate tables. I don't see how the relationships are maintained because there are no relationships as of yet.

So, in short:

-How do I ensure that each original record can be reconstituted from the separate tables?

and

-What is the best way to append the new data to the database when future data-dumps are available?

[Rabbit]

It's difficult to say without knowing anything about the design. Please post the current unnormalized design and your vision of the normalized design.

Fifty fields is a lot so if you want to start with a subset of those fields as a simple exercise, we can do that as well. So pick the fields that would make up two to three related tables and we can work on that subset. But if you want to do it all in one go we can do that as well.

[Rabbit]

Just to get you thinking about the process, here is a simple example.

Imagine you have this unnormalized data:

ClientName PhoneNum1  PhoneNum2
Bob        5551234567 5559999999
Susan      8884561278

The normalized design would be this:

Clients
ClientID - Autonumber
ClientName - Text(50)
 
 PhoneNum
ClientID - FK
Num - Text(10)

To normalize, you would:

1. Select distinct client names from the unnormalized and insert it into Clients.
2. Select PhoneNum1 from the unnormalized, join to Clients to get ClientID and insert into PhoneNum where the phone number isn't blank.
3. Repeat above for PhoneNum2.

To denormalize, you would join the tables together and run a crosstab query.

[nico5038]

To get an idea you could use the analyse feature of Access under the Database tools. This will show some possible normalization, but it won't be the "silver bullet". Some normalisation expertise will still be needed.
For that check out out Articles section.

Nic;o)

[ADezii]

@meierrain:
Could you possible Upload an Access Database consisting of, say the first 500 Records from your Data? A Picture is worth a thousand words, especially so in this case (LOL).