January 15, 2018 at 5:18 am
Hi all
I'm setting up a SQL Database to host consolidated data from a number of separate databases. I think I've sorted the structure out, but I not sure on how to best set up the database for updates.
For a little background, we have a number of Access databases that hold customer information for a given region. I'm trying to consolidate this information into a SQL Database with a very similar structure, but with consolidated keys that include the branch ID. That way I can store the same customer number for each branch in one database.
We're looking to update that daily, but my question is, what is the best way to set up SQL Server? Here are some of the limitations I have.
I've thought about a few options so far, which each have pros and cons, so I thought I'd ask the experts (you guys) what the best course of action would be. Here is what I've thought of so far
That's what I've come up with so far, and I think option 4 is probably the best. I've not used temp tables before, but I think it'd work.
Does anyone else have a better solution?
Thank in advance
Huw
January 15, 2018 at 5:35 am
Hi Huw
I think the first thing to take into consideration is, do you want to be able to store any of the changes to those customers?
ie. If I customer's mobile number or address changes, do you want to be able to see what it was at a certain point in time?
January 15, 2018 at 5:42 am
I won't want to change any data in those fields. There are only a handful of things that will need to be recorded in this database, and I'll keep them in a separate table/tables and update them back into the Access database at regular intervals.
Huw
January 15, 2018 at 5:55 am
What is the purpose of writing the data from your new central database back to the individual Access databases?
If you are not interested in recording any history (changes over time) then go with option 2 (Truncate the tables and insert the latest data)
If you do want to be able to track any changes that happened then look at the principals behind SCD (Slowly Changing Dimension) tables:
https://www.kimballgroup.com/2008/08/slowly-changing-dimensions/
https://www.kimballgroup.com/2008/11/design-tip-107-using-the-sql-merge-statement-for-slowly-changing-dimension-processing/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply