0

I'm using SQL Server 2019. I have a "MasterDB" database that is exposed to GUI application. I'm also going to have as many as 40 user database like "User1DB", "User2DB", etc. And all these user databases will have "exact same" schema and tables.

I have a requirement to copy tables data (overwriting target) from one user database (let's say "User1DB") to the other (say "User2DB"). I need to do this from within "MasterDB" database since the GUI client app going to have access to only this database. How to handle this dynamic situation? I'd prefer static SQL (in form of Stored Procedures) rather than dynamic SQL.

Any suggestion will greatly be appreciated. Thanks.

DataPy
  • 209
  • 2
  • 10
  • 2
    Sounds like a terrible design? Why are you creating a separate database per user? However if you need to, what specifically don't you understand? delete followed by insert? – Dale K Jan 28 '21 at 22:28
  • Dale K, we need to source control data - think of when you clone a git repo, you get user specific/decentralized full branch with histories, etc. – DataPy Jan 28 '21 at 22:33
  • But why not source control it with a single database with the userid column in the relevant records? – Dale K Jan 28 '21 at 22:33
  • In the end, you'll find that you're creating 40 times the amount of work for yourself if you go with this path. if you need to "source control" data, have some control tables which allow a user to "check out" or lock data, and retain an audit history of change. Consider this: if a user connects to the master db, how do you know which user? which db will contain this users data? and if you only allow access to the masterdb, will you have a passthrough view or SP for each user? 40x the objects just to manage some data? – Jeremy Jan 28 '21 at 22:52
  • I am a bit confuse with your design. How does the `user` going to access `User1DB` or `User2DB` when the GUI only acess the `MasterDB` ? – Squirrel Jan 29 '21 at 03:55
  • Guys (Dale K, Jrermy, Squirrel & spencer741), thank you so much for your feedbacks. This is something new we are designing, and I taken for granted some assumptions. I'll tweak my design ideas and see how I can make all this in just "one database"...again Thanks. – DataPy Jan 29 '21 at 20:48

1 Answers1

0

Check out this question here for transferring data from one database to another.

Aside from that, I agree with @DaleK here. There is no real reason to have a database per user if we are making the assumption that a user is someone who is logging into your frontend app.

I can somewhat understand replicating your schema per customer if you are running some multi-billion record enterprise application where you physically have so much data per customer that it makes sense to split it up, but based on your question that doesn't seem to be the case.

So, if our assumptions are correct, you just need to have a user table, where your fields might be...

UserTable
UserId
FName
LName
EmailAddress
...

Edit: I see in the comments you are referring to "source control data" ... I suggest you study up on databases and how they're meant to be designed, implemented, and how data should be transacted. There are a ton of great articles and books out there on this with a simple Google search.

If you are looking to replicate your data for backup purposes, look into some data warehouse design principles, maybe creating a separate datastore in a different geographic region for that. The latter is a very complex subject to which I can't go over in this answer, but it sounds like that goes far beyond your current needs. My suggestion is to backtrack and hash out the needs for your application, while understanding some of the fundamentals of databases (or different methods of storing data). Implement something and then see where it can be expanded upon / refactored.

Beyond that, I can't be more detailed than the original question you posted. Hope this helps.

spencer741
  • 965
  • 1
  • 10
  • 22