Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    2

    Question Practical Steps in Refactoring "Flat Table"

    I have "inherited" an Alumni DB in MS-Access, which is actually nothing more than a flat table with approximately 2000 records and 35 columns. The Alumni directory has been maintained for years by a single person on a voluntary basis, but he is leaving abroad shortly. Our Alumni directory is now supposed to become the back-end of a full-fledged web application (e.g., using MySQL). However I am more an application developer than a DB expert. Now, assuming that I have normalized this flat table and put down an appropriate DB schema on paper, can anyone provide some info as how to actually do the refactoring process ? Any pointers to useful (and free) tools, tutorials, articles, etc. would be greatly appreciated !

    Thanks,
    Yves

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if by "refactoring" you mean how do you get the data in the single flat table into a new set of properly normalized tables, you would do that by running a series of simple queries

    do your new design right in Access, and then take a screen shot of the relationships if you want us to provide comments
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by y_v_e_s
    Any pointers to useful (and free) tools, tutorials, articles, etc. would be greatly appreciated !
    You should not need much more than a rudimentary grasp of SQL (probably...).

    http://www.w3schools.com/sql/default.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2006
    Posts
    2
    Ok, thanks! For a moment I thought that visual tools could be used to deconstruct the flat table etc. (with SQL in the background). So I will brush up my knowledge SQL a little, and may come back if any question would arise in the process.

    Yves

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Issue 1: If you're using surrogate keys, you'll want to add an autonumber field to your table. I'm not sure if Access will fill it for you, if not, copy the schema of the table to new_table, add the autonumber field, and run:

    INSERT INTO new_table SELECT * FROM old_table

    Now you've got all your rows numbered.

    Issue 2: Futzing with types. Rather than creating the tables from scratch, I'd set up SELECT queries to start with. Then just copy those queries to actual tables.

    That way you've got an actual table to work with.

    Issue 3: Regression. Basically, you want to make sure that you don't corrupt any data. The best technique is to create some queries that get statistics on your data. For example, if you have 800 people to start with, you need to end up with 800 people. Get averages of stuff like birthdays. You can also get the average length of different fields. Those numbers are very sensitive, so if you mess up one record, it's likely your average will be off just a little bit. If you've accidentally truncated a field, the max length will definitely be off.

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by y_v_e_s
    For a moment I thought that visual tools could be used to deconstruct the flat table etc.
    The standard query tool is visual.

    Also, there's a table-splitting wizard or something in Access, but I've never used it. (It has to guess what your dependencies are based on the data, but I believe you can correct it.)

Posting Permissions

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