pawnit
asked on
Approach to handling ID fields database importing
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.
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.
kmslogic
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.
ASKER
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.
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.
ASKER
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.
I'm talking about adding an additional field to the table, not doing some kind of string concatenation.
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.
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.
ASKER
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.
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.
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.
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.
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.
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.
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.