Flat database into relational

[JeanR]

Hello,

I am wondering about something. A database we have was created as a flat database. I would like to make it relational but unfortunately no unique identifier was included. Is there a way to add one now.. for example put in a text field and auto enter a serial number then replace in other records.. Any chance this might work. Thanks.

Jean

[Fenton]

Certainly. But it would require some understanding of relationships. Basically, since it is "flat", you are relying more or less on "implied" self-relationships. By that I mean, you are entering something like a company name over and over, in say something like invoice records. You are assuming that they could be found together with a Find.

But it is only implicit, because not only is there no consistent ID to tie them together, you are dependent on the name being typed the same in the records. A standard Find is actually pretty "sloppy" (or "forgiving", depending on what you want it to do). A relationship, on the other hand is precise.

The first thing to do is to decide what tables you need for your structure. Lets say "Companies".

Show All Records

Sort by Company

Export Records, Group by Company field

These should be unique entries for Company. If you see 2 (or more) that should be the same company, but are slightly different, then go fix them in the main file. Repeat process until they are clean.

You can do much the same by going into Find mode, then putting your cursor in the Company field, and hitting Control-I (Windows) or Command-I (Mac). That will show you the "index" of the field. You'll see any funky spellings there also. Find them and fix them.

When you're all done you can export the grouped companies out, then Import into a Companies table, with real Serial ID auto-enter incrementing field. Then use a temporary relationship to the other table, based on company name. Use that to bring the new ID into the old table; via a Replace, by calculation, temp name relationship::ID field.

Rinse and repeat with whatever other things you got.