CODEWITHSUNDEEP
asp.netsqlsql-server
Diomedes
Diomedes

Reputation: 658

One database to log in, use different database depending on person

I'm planning to do a website where people have to log in to access it. Easy enought. Now, once they log in, depending on information in their membership, I want the website do use a specific database.

So I could have 100 database, with the same structure, but with different data for each specific group of users - Instead of having one large database and determinen which data they can access with an identifier column.

Not sure I'm making sense here. Basically, one website, mutiple websites depending on membership.

Is there a way I can to do this? Is there another approach that would be effecient?

Thanks!!

Upvotes: 1

Views: 159

Answers (1)

StuartLC
StuartLC

Reputation: 107247

If I understand correctly, you still have just the one Membership database, and after authentication, thereafter depending on some criteria, your application switches connection string depending on the user, but it is just the one application.

Technically, this isn't particularly challenging, given that all of the common data access mechanisms (Linq2SQL, EntityFramework, SQLClient etc) all allow the connection string to be provided at run time. You could either add 100 connection strings to your web.config, or store them elsewhere (e.g. your initial Membership DB).

However, that said, in the long run you might find it easier to filter data specific based on the user's associations to the data, and just retain the one database.

Consider

  • What happens if a user is allowed to access more than one 'database' worth of data. In the one database model, its as simple as adding another link in a user:data many:many table.
  • You now have 100 databases to backup, index etc (?license!).
  • Reports which consolidate data now have to join across 100 databases
  • SQL caching and connection pooling won't be as effective with this many databases.
  • What if you need to change the schema or a stored proc? You now need to duplicate this everywhere.

etc.

Upvotes: 2

Related Questions