CODEWITHSUNDEEP
c#asp.netasp.net-mvcentity-frameworkvisual-studio
Sigmundur
Sigmundur

Reputation: 897

Displaying data only relevant to the user logged in. Asp.net MVC5.

I am starting work on a hobby project that simply keeps a list of contact information. Tons of tutorials out there but I am looking for individual based contact list. So when someone logs in using the simple log in provided, that user will only see contacts associated with him/her. Later on I would like to add OAuth but that is for another day.

How would I tie the relevant contact information to the specific log in information?

My data model looks like this: 

namespace ContactManager.Models
{
    public class Contact
    {
        public int ContactId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        [DataType(DataType.EmailAddress)]
        public string Email { get; set; }
    }
}

First I thought about having a column which associates a user with it. Something like storing the email of the user in a column with each contact info belonging to each user. But that seems like a lot of hassle.

Then I thought about having a table for each user. Which makes the database a bit cleaner but a lot of tables if there are a lot of users. I am not expecting a lot of users, I would just rather do it the "correct" way instead of some hack that assumes few users.

So again, how do I separate each users contact information so I can display the relevant info for each user?

Cheers!

P.S. I followed this tutorial to start with and have that working to the extend that I need. (No OAuth login for example)

Upvotes: 0

Views: 1683

Answers (1)

ErikE
ErikE

Reputation: 50201

You need a User table and a UserContact table. Assuming SQL Server:

CREATE TABLE dbo.User (
   UserId int identity(1,1) NOT NULL CONSTRAINT PK_User PRIMARY KEY CLUSTERED,
   Name varchar(50) NOT NULL CONSTRAINT UQ_User_Name UNIQUE,
   EmailAddress varchar(254)
);

CREATE TABLE UserContact (
   // Do NOT add a surrogate key to this table. No identity column!
   UserId int NOT NULL
      CONSTRAINT FK_UserContact_UserId FOREIGN KEY REFERENCES dbo.User(UserId),
   ContactId int NOT NULL
      CONSTRAINT FK_UserContact_ContactId FOREIGN KEY REFERENCES dbo.Contact(ContactId),
   CONSTRAINT PK_UserContact PRIMARY KEY CLUSTERED (UserId, ContactId)
);

Then you link users to contacts by putting a row into this intermediate table with the IDs of the related entities (called a many-to-many join table).

One more thing to think about is that Users themselves have contact information. So you might consider doing this instead:

CREATE TABLE dbo.User (
   UserId int NOT NULL
      CONSTRAINT FK_User_UserId_ContactId FOREIGN KEY REFERENCE dbo.Contact(ContactId),
      CONSTRAINT PK_User PRIMARY KEY CLUSTERED
);

This means that before anyone can be a user, their information must be entered as a contact first. Then, you insert the ContactId into the User table as their UserId. Finally, you use the same UserContact table with the same foreign key to this new User table. By doing it this way, you ensure that only people who have been marked as users in your system can be associated with contacts. You likely wouldn't want to be able to relate any contact to any other contact—the vast majority of your contacts won't be loggin into your system.

I frankly think this latter idea is a better scheme. It is called the "supertype/subtype" pattern, and is much like inheritance in structured programming, where a Contact can also be a User, and the User class inherits from the Contact class. You can even make this inheritance work exactly the same way in your code!

public class User : Contact {
}

var contact = GetSomeContact();
var user = contact as User;
if (user != null) {
    // This is a user! Do some special handling
}

You might also consider, instead, having them both inherit from a Party class (though possibly maintaining the "User is a subclass of Contact" relationship). If you put companies and organizations into your contact database, they also would be subclasses of the Party class. See A Universal Person and Organization Data Model for some more ideas.

Update

Given the new information that you are using a pre-existing dbo.AspNetUsers table, you could either modify that table so it functions like the second User table I suggested above, or you could just omit the new User table, and use the AspNetUsers table as-is with the many-to-many join table pointing to it instead of to User as I suggested at first.

Upvotes: 1

Related Questions