2

How to make a blank database for every registered user or it is already implemented in ASP MVC4? Will they have different databases? For example, in an application, which stores contacts, I would like, that different users could create their own contact list.

And if I have ContactsController, which I want only registered users to be edited, should I write something like that?

[Authorize]
public class ContactsController : Controller

EDIT O thanks Bhushan, I didn't know that User ID will store different data.In Contact table I have ContactId, which is associated with a contact data and it has a primary key, should I add for example UserId in the same Contact table to associate it with registered user ID? And should UserId have primary key?

tereško
  • 58,060
  • 25
  • 98
  • 150
Kirill Ryzhkov
  • 522
  • 2
  • 9
  • 23
  • 1
    Why would you want to do that? o_O If you want every user to have it's own contacts then let the contacts table have a foreign key with the user id. – Corak May 24 '13 at 11:14

3 Answers3

4

If I understand your question properly:

different users could create their own contact list

Above task doesn't require a separate database, You can associate Contacts with Users by adding a field ContactOwner or similar name in the Contact table which will store a User ID.

So your contacts table can look like following: (TABLE NAME: CONTACTS)

|ContactID       | ContactType | ContactName | ContactOwner |

|0123456789      | MOBILE      | Mr. ABC     | USER1        |
|email@email.com | EMAIL       | Mr. ABC     | USER1        |
|0123456789      | MOBILE      | Mr. PQR     | USER1        |
|0123456789      | MOBILE      | Mr. XYZ     | USER1        |
|0123456789      | MOBILE      | Mr. LMN     | USER2        |
|0123456789      | MOBILE      | Mr. AAA     | USER3        |

So as you can see the sample table above you can identify which ContactID belongs to which User? Or which are the contacts created by which User? In this case you have to make ContactOwner primary key(Composite key) with other primary key(s), to make sure that one contact can be Owned/Created by multiple Users.

Update 1

To get contacts created by specific user you can write a query like:

Select * from CONTACTS where ContactOwner = 'USER1'

Above query will give you the contacts which are Created/Owned by USER1, and not the ones created by other users. so your result will contain the following records using above query:

|ContactID       | ContactType | ContactName | ContactOwner |

|0123456789      | MOBILE      | Mr. ABC     | USER1        |
|email@email.com | EMAIL       | Mr. ABC     | USER1        |
|0123456789      | MOBILE      | Mr. PQR     | USER1        |
|0123456789      | MOBILE      | Mr. XYZ     | USER1        |

Update 2

Your query will be a dynamic one in which ContactOwner will be the USER who is logged in. This and This might help you to write dynamic query (prepared statement)[Note: since I am a Java developer so I don't know the suitable syntax in C# for writing the dynamic query.]

Community
  • 1
  • 1
Bhushan
  • 6,151
  • 13
  • 58
  • 91
  • But will each user see only his own contact list in this case? I don't want that user can see contacts, created by other users – Kirill Ryzhkov May 24 '13 at 11:48
  • Ok, thanks for explanation, but if, for example, USER2 is logged in, does it mean that he will see the USER1 contacts? Maybe, it is possible to write a dynamic query? – Kirill Ryzhkov May 24 '13 at 12:06
  • @KirillRyzhkov please see my `Update 2`, and still if you have a question then let me know. – Bhushan May 24 '13 at 12:33
4

If you want to have an empty DB for each user, just add as part of the registration process creating new database. you can easily do that with SQL statements (CRATE DATABASE, followed by CREATE TABLE and other create statements to create your schema). you can also use SMO do duplicate existing template databse. you can also create it using entity framework database-first (with few tweaks, like modifying your connection string). BUT, all those options are not recommended for your scenario.

If you want to give each user his contact list, just add to the "Contacts" table "OwnerUserID" column, or something like that, that will have FK relationship to your users table, and will include the user which this row belongs to. In your queries, just filter by this and show each user his own contact list.

This way it'll be much easier for you to manage things and add/modify things in the future. also, working with multiple databases like you asked in your questions will give you a lot of pain, with almost no benefits (in most scenarios).

Best of luck.

Shahar Gvirtz
  • 2,418
  • 1
  • 14
  • 17
0

FYI, in e.g. SQL Server 2012, the maximum number of databases is 32,767.

Please don't make a new database for every user, it defies basic logic and the point of having relational databases in the first place. Use tables and foreign keys and whatnot.

Wim Ombelets
  • 5,097
  • 3
  • 39
  • 55