January 29, 2010 at 2:03 am
Hey,
I am facing the following requirements from my client.
- My client has N shops. He has installed my application in each of the shop, therefore he has N instances of my application.
- Now he is asking me some sort of centralized application / database which will show statistics from all his shops.
- All the databases have exactly the same structure, so they are identical excepting the data which are shop specific
I am stucked at the modelling of the central database. The perfect thing would be to keep exactly the same structure as all the other instances, in order to be able to reuse the same code. But in this case I cannot just go and copy data from 10 databases into a single one, because I will have to face the integrity problems, like ProductID = 5 will be duplicated and can actually represent different Product in the database.
Another solution would be to copy views of data from all the databases to the central database to some storage tables (example copy ProductName, ProductPrice, Product SKU from all the databases into the Reporting_ProductList table of the central database), but this way I cannot reuse the existing reports.
I have a feeling that this issue might be a quite popular issue and there should be some generic approach / architecture on this.
Please advise!
January 29, 2010 at 3:36 pm
You could replicate to a central server, but you'd have to address ID issues. Most centralization schemes have to solve this problem and there isn't a good way to do it that's generic. If you used identities, you could partition identities in each instance, but that involves thinking ahead before you install and that could be a major headache.
What I might do is use SSIS to move the data from each location, but transform it to include some location identifier and add that to tables in your central db.
January 29, 2010 at 3:50 pm
Or add a ShopID to the appropriate tables so you know which records belong to which shop. Would require some changes to the indexing probably as well.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply