1

I have a table that looks like this:

Person  Project1 Project2 Project3 WorkedWith1  WorkedWith2  WorkedWith3
John    A        B        Null     Ann          Mary         Null
Ann     A        B        C        Jeff         Null         Null
....

I am looking for a tool that would help me to convert it to 1NF, without me writing any code.

Specifically, in this example, I would tell the tool that I want to rearrange the data into tables Persons, Projects, Assigned (which links Persons to Projects in a many-to-many relationship), and WorkedWith (which links Persons to Persons in a many-to-many relationship). All I want the tool to do is move the data around according to these instructions.

I don't really care if this tool is part of some DBMS, or simply works with plain text (CSV) files - without touching any specific database. I tried Table Analyzer in MS Access 2010, but unless I missed something, it cannot solve this particular task. (It works perfectly in simpler cases though.)

Note: I am familiar with normalization; I'm not trying to learn anything - just trying to save myself time. I also understand why an all-purpose tool for database normalization doesn't exist. I am hoping that my case is so simple and common that there might be a tool that would handle it. I also don't mind learning a relatively sophisticated tool.

max
  • 49,282
  • 56
  • 208
  • 355
  • The best tool is to analyze and code it yourself. In that way, you will learn to teach yourself database normalization without depending on a tool. Cheers :D – John Woo Oct 28 '12 at 06:29
  • @JohnWoo: I have reasonable experience with database normalization. I am not trying to teach myself anything here. I am looking for a tool to save myself some time. – max Oct 28 '12 at 06:34
  • @anonymous downvoter: Thank you for providing your feedback, even though it's negative. May I ask you to clarify why my question is "unclear or not useful" (I believe that's what downvoting implies)? – max Oct 28 '12 at 06:35

1 Answers1

2

I believe that what you want is an ETL software.
As far as I know, you will need to pre-create your destination tables, but an ETL can split, recombine and load data from one database (or file) to another.

It may be overkill for your purpose though, the learning curve for using ETL software will probably be a lot more than writing some VBA and some queries to help transfer the data from one form into another, but if this is something you have to do often, it may still be worth it.

In your case though, I would most likely just create the destination tables and make multiple passes over the input table to extract data a piece at a time, fill in the Persons and Projects tables with the distinct data from the original columns, then re-create the relationships in a separate table.

When doing this kind of work in VBA, I have found the Dictionary to be invaluable for keeping track of relationships.

Community
  • 1
  • 1
Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • Thank you, this is helpful. I didn't know what ETL software was all about. I now added ETL tag to the question. Obviously, if there's no tool with a light learning curve, I may indeed just write my own queries - but it's still good to know what the options are. – max Oct 28 '12 at 06:59