Approach to handling ID fields database importing

pawnit
pawnit used Ask the Experts™
on
G'day Guys,

I'm just wanting to get some ideas on what approach people would take when faced with the same situation I am faced with at the moment.

We offer a software as a service solution to our clients; we use a MSSQL back end database. The majority of our clients previously used and old system written in Visual Fox Pro with a dbf database.

We would like to offer a facility which will import the data from the old VFP .dbf files into our MSSQL database.  I would have to write some form of script or something of the like as the data stored in these old .dbf's is all over the place and will need to be manipulated to fit into our database; Also what would be the best approach to handling the auto generated ID of these dbf tables. I cannot just import then as they will cause conflicts with data already in the MSSQL database as well as import of other customers databases.

Any tip or ideas would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
When you are importing data include a client_id that is unique to each client you are importing.  The combination of that + their old ID will be unique, so you can use it for relationships in the old data, etc.

Author

Commented:
Hi Kmslogic,

I though of that however if i was to use a prefix starting with 1 and append it to an id of 1 the first id will become 11. if we look down to the 1001th record that becomes record 11001 this will conflict when you come to using 1100 as a prefix so when you add it to record 1 it also becomes 11001.

I know it might not be a change that we will get up to 1100 in prefixes but it proves that adding a prefix can still create duplicate id.

Author

Commented:
Or even simpler. client 2 on row 214 will create ID of 2214 as well as client 22 on row 14 will create ID 2214 as well.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
I'm talking about adding an additional field to the table, not doing some kind of string concatenation.
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
I did a small script once for a similar activity and I always prefer to use the standards of the latest database I am using.

So while importing the new records, I collect and the new ID's and map them to the old one. So as I am adding records from other tables, I use the new IDs.

So for example, I import clients, products, suppliers, ... Then I import purchase orders, invoices and so on so forth.

Author

Commented:
I'm not to sure that will work, they are both relational databases.  For example there is a customers table with a one to many relationship to the transactions table and then transactions table have a one to many relationship to the transaction items table.

If I take your approach I would have to add a new column to every table in the MSSQL database where there are relationships and then change all the SQL statements in the Application which would be a complete nightmare. Not to mention it will stuff up all the existing ID fields in the database.
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
I am not sure how you created your database.

When I created my MIS database, I used character codes for the clients. If a client is called "Client X" then his code would look something like "C12345". This was based on a standard in FoxPro samples way back in late 80s.

So when I wrote the script for my client to move the data to MSSQL way back, I wrote it in such a way that "Client X" got a primary key of 1 (being first record added). In a map file which I used temporarily, I map "C12345" to 1. When the records are in the new database, C12345 does not exist anymore.

I am not sure if this applies to you. But this is how I did that one. Just an idea.
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
I am not sure how you created your database.

When I created my MIS database, I used character codes for the clients. If a client is called "Client X" then his code would look something like "C12345". This was based on a standard in FoxPro samples way back in late 80s.

So when I wrote the script for my client to move the data to MSSQL way back, I wrote it in such a way that "Client X" got a primary key of 1 (being first record added). In a map file which I used temporarily, I map "C12345" to 1. When the records are in the new database, C12345 does not exist anymore.

I am not sure if this applies to you. But this is how I did that one. Just an idea.

Commented:
Well I'm assuming you are going to put the records for multiple clients in one table.  If that is the case you are going to have to change all the SQL statements in the application anyhow so that they don't see the other clients that are in the table.  Whatever way you do that (either by joining on both the client_id fields and the existing ID fields when relating the tables, or by filtering the table beforehand for a specific client ID and then just using the original SQL)

You really didn't describe what service you are really providing.  You said importing the data into your database from their old FoxPro systems, but that itself isn't really a service unless they are going to access or get the data back out of your system somehow.  I'd guess you are going to provide them with some kind of cloud-like access to their old data but it's not clear how their original old system will still be able to work with this new cloud-based version even if you only have one client and just leave everything pretty much identical on the SQL server side.

Could you give some more details and maybe I can give some concrete examples of what I'm talking about...
Software Developer
Commented:
If you want to keep the data of your customers seperate anyway, why not skip all the problems and create a database per customer?

You might also create schemas in the one database and let each client have the same set of tables in their own schema, instead of the default dbo schema:

customer1.customers, customer1.orders
customer2.customers, customer2.orders
...
You can reuse the same table names in a different schema. Together with a schema default and a set of grants the users will only see their schema.

See http://msdn.microsoft.com/en-us/library/dd207005.aspx
and CREATE SCHEMA: http://msdn.microsoft.com/en-us/library/ms189462.aspx

You then set the default schema of database users and they will see their own part of the database only. Just make sure none of your customers have the sysadmin server role, their default schema always is set as dbo, see http://stackoverflow.com/questions/3806245/sql-server-schema-and-default-schema

Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial