Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xyz_ID_xyz (Access 97 SR2)

    I'm being force to normalize a very old database, and the table splitting wizards are killing me !!

    The table has 35 fields <img src=/S/scream.gif border=0 alt=scream width=15 height=15> and no end of typos as data.
    Worse yet, it has an important field that is a memo type and is not transferrable.

    After sufferring through that wizard's series of non-resizable windows, where you pick the values that conflict with existing values, I finally had my tables and that new all-purpose query.
    Now I have to hand edit the SQL code behind the Look-up fields to get what I want .
    Is there a better way to normalize?
    I need some serious help...any and all links appreciated <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xyz_ID_xyz (Access 97 SR2)

    Here's what I usually do.

    1) I figure out how the data needs to be broken out and design tables to hold the relational data, but I include a temporary field in each table to hold the key to the non-normalized table (let's call it Fred for convenience) . That will be discarded later (or not, at least it won't be used later on), but it's needed to create temporary joins to parse the data out.

    2) I add an autonumber primary key to the Fred table if it isn't already there from an import. This will be inserted into that field in the relational tables when data is appended to them.

    3) I add fields to Fred for the primary key for each table that a record will wind up populating.

    4) I add any fields I need for parsing information out (i.e., firstname, lastname, middleinitial, etc.) from the existing data in Fred.

    4) I determine the "core" information in the table. That is, if you have a record with a name, address, company, telephone, etc., which piece of information is going to the the core that the rest of the data relates to?

    In mixed information, i.e., organization and individual, I usually create another table called something like tblDBMember, which is the core element and contains an entry for each person and each organization in the database, along with a field that says which is which. So in that case, I would have a field in the record for the DBMemberID, as well as keys for the AddressID, the PhoneID, etc. The DBMemberID would be the one used to relate the core record to all the other information like address and phone number.

    5) I parse out the core data first and append that to the core table, inserting the PK from Fred into that temporary field in the target table.

    6) I update Fred with the PK from the core table based on a join between Fred and the core table on the FredID.

    After that, I do much the same for each of the other tables, populating them one at a time and then updating Fred. The reason for the update to Fred is that it provides keys that I may need to populate tables based on other joins rather than the core table. Additionally, by updating Fred I can easily undo the damage without having to start over from scratch if I get fat fingers.

    It isn't exciting, and it can be a lot of work, but it isn't rocket science either. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> If I have to do this very often (I used to work for a company that routinely got mailing lists of from 50K to 150K names that had to be parsed out like this), I save the queries that I build, build an interface for it, and save myself some work down the road.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •