4 Replies Latest reply on Nov 22, 2011 9:13 AM by philmodjunk

    Importing old flat file into a new relational database.

    silba2021

      Title

      Importing old flat file into a new relational database.

      Post

      Our old database had one file for all our products. Our new database is going to use a relational database but I am having trouble figuring out how to bring in the data.

      For example the new database will have products and desingers in two tables, the problems is that there is going to be a join table called assignments (since one product can have multiple designers and one designer can work on multiple products).

      I think I can fiugre it out if it were a direct relationship but the join table is throwing me, any help would be appreciated.

        • 1. Re: Importing old flat file into a new relational database.
          philmodjunk

          Can you tell us more about the data in that "flat file" DB?

          You can probably do this by importing the data more than once, but a script or Replace Field contents controlled batch operation may be needed to finish the job of properly linking all your data...

          • 2. Re: Importing old flat file into a new relational database.
            silba2021

            The old flat file has all the information of a product in one table. This includes fields for product, designers, developers and other details like manufacturing dates and vendors.

            In the new database I had no problem seperating the developers and products into two tables, since it is a one to many relationship. I imported the entire flat file into the products table , connected the developers table and the products table using the name of the developer, then imported only the developer names into the developers table. Then created a serialized primary key in the developers table and in the products table created a foreign key for developers and did a Replace Field using the Primary key for developers. Then redefined the relationship based on the primary and foreign keys. I wouldn't mind hearing a better way to do this.

            The problem of course is to acheive the same result with the designer and products table with a join table (assignments) in between.

            • 3. Re: Importing old flat file into a new relational database.
              DPmax

              I had to do this once years ago, I added a fields in the old non-relational db that was (for example in your case) _id (this is for the base/main record) _id_developers, _id_products... and so on. I then did a Replace Contents for each using a serial value, like p000001 for Products and d000001 for the Developers and so on.

              Then in the new database on the main table I had _id (which later I serialized using auto-enter), on the Products table there was also _id as well as _id_products... and the the same with Developers and so on.

              Next, Import your data to the new tables making sure that ALL the tables get the _id data from your original DB, it will take one import pass per table.

              From here on out the _id filed will be used as your relational link for all tables to the main table (and inbetween other relationships as well)

              I hope that make sence I'm wrapping up a LONG day.

              • 4. Re: Importing old flat file into a new relational database.
                philmodjunk

                silba2021,

                You sound like you have an excellent grasp of the basics for setting this up for the first part of your process. The key question is that when you look at the data in your flat file, how do you tell what what product to link to a given (possible list) of designers?

                There could be a number of ways that was done in the flat file and we have to start with that format to devise a method to populate the join table.