ASP - New instance of SQL database per user?

Question

I have been struggling with this for some time, and can't seem to find any info.

I have created an online point of sales system using an SQL database, EntityFramework, ASP and .Net4 in C#. My previous programming experience has been with OpenGL and DirectX, so this is all very new to me.

Users from a single company will log into the site using a single account. They can then use the online till and backoffice. Thus far the software has been developed using a single SQL database. What I would like to do is have the application create and use a different instance of the SQL database per account.

Does this sound sensible/possible, or would you expect to have a single huge database for all users? - Note: there could potentially be a huge number of users which each store a lot of information and each user will only need access to their own database.

Any suggestions would be greatly appreciated

Thanks,

What database system, and which version?? SQL is just the Structured Query Language - a language used by many database systems - SQL is NOT a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... — marc_s
– marc_s, Commented May 4, 2012 at 12:18
This seems like overkill to me - most mature databases can handle a very large amount of data with no problems so I really don't think you will have any problems but as above it is hard to say much more without knowing what database you are using? SQL Server? Something else? — Kevin Main
– Kevin Main, Commented May 4, 2012 at 12:27

marc_s · Accepted Answer · 2012-05-04 12:27:45Z

2

I think this is not a good approach, as if there will be any scheme changes in you will have to keep synchronize each database instance.

edited May 4, 2012 at 12:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 4, 2012 at 12:26

Ovais Khatri

3,21118 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Etherton · Accepted Answer · 2012-05-04 12:33:02Z

1

Does this sound sensible/possible, or would you expect to have a single huge database for all users? - Note: there could potentially be a huge number of users which each store a lot of information and each user will only need access to their own database.

This is possible, but it is not at all sensible. Users don't access databases, applications do. Separating a full database per user only makes sense if your application is offering users the capability of creating their own data structures within the application (such as a hosting provider would do). If it is simply meant to house multiple identical structures keyed off each user, then you'll be better off spending your time normalizing a proper relational structure (an assumption since you mentioned you're using SQL) and optimizing queries/stored procedures to make data retrieval/insertion fast.

RDBMS is perfectly capable of handling huge amounts of users and large subsets of data if you normalize it effectively keying each structure appropriately (in most cases by the userkey).

answered May 4, 2012 at 12:33

Joel Etherton

37.5k10 gold badges92 silver badges106 bronze badges

3 Comments

Jakub Konecki Over a year ago

+1 - and there are ways to provide custom fields for individual users with-in single database

user1374841 Over a year ago

Thanks - So a single database is sounding like the most sensible option, but what about security? Can users have access restricted to only read entries related to them? The plan was to offer a backup facility and be able to restore individual users data to a previous state. With separate databases this would just involve keeping an archive of their database. Can this be achieved with a single database solution?

Joel Etherton Over a year ago

@user1374841: With having records normalized by user key, permission to records can be restricted in any number of ways. This restriction becomes controlled at the application level rather than at the database level, however. Backups can still be achieved, but I don't have the dba experience to suggest an appropriate method.

Internet Engineer · Accepted Answer · 2012-05-04 12:31:22Z

0

The best approach is to have a single huge database for all users.

SQL Server will only support 32,767 databases per instance.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

answered May 4, 2012 at 12:31

Internet Engineer

2,54410 gold badges41 silver badges54 bronze badges

Collectives™ on Stack Overflow

ASP - New instance of SQL database per user?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related