locked
Relation between users with messages RRS feed

  • Question

  • Hello,

    I have a table of Users:

        create table dbo.User
        (
           Id int identity not null,
           Name nvarchar (400) null,
           // Other coluns as Login, Password, etc.
        )
    
        create table dbo.Role
        (
           Id int identity not null,
           Name nvarchar (20) null
        )
    
        create table dbo.UserRole
        (
           UserId int not null,
           RoleId int not null
        )
    
    Each user has one or more roles.


    I need to relate users one to each other in periods of type.

    For example, one professor to its students during 4 months in 2015.

    And I also need to track messages between users.

    One of my ideas would be the following:

        create table dbo.UserToUser
        (
           FirstUserId int not null,
           SecondUserId int not null,
           StartDate datetime not null,
           EndDate datetime not null
        )
    Being both ProfessorId and StudentId FK's of Users table.
        create table dbo.Message
        (
           ToId int not null,
           FromId int not null,
           Text nvarchar(max) not null,
           Created datetime not null
        )
    Being both ToId and FromId FK's of Users table.

    Should this be done in another way?

    Thank You,

    Tuesday, February 10, 2015 5:27 PM

Answers

  • Review the following design:

    create table dbo.Users
        (
           Id int identity PRIMARY KEY,
           Name nvarchar (400)not null UNIQUE,
          -- // Other coluns as Login, Password, etc.
        )
    
        create table dbo.Role
        (
           Id int identity PRIMARY KEY,
           Name nvarchar (20) not null UNIQUE
        )
    
        create table dbo.UserRole
        (
           UserId int not null REFERENCES Users,
           RoleId int not null REFERENCES Role,
    	   PRIMARY KEY (UserId, RoleID)
        )
    
    	  create table dbo.UserToUser
        (
           FirstUserId int not null REFERENCES Users,
           SecondUserId int not null REFERENCES Users,
           StartDate datetime not null,
           EndDate datetime not null,
    	   PRIMARY KEY (StartDate, FirstUserID, SecondUserID)
        )
    
    	create table dbo.Message
        (
           ToId int not null  REFERENCES Users,
           FromId int not null  REFERENCES Users,
           Text nvarchar(max) not null,
           Created datetime not null,
    	   PRIMARY KEY(ToId, FromId, Created)
        )




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Tuesday, February 10, 2015 9:42 PM