locked
Table relationship question . How to condense a one to "very many" design? RRS feed

  • Question

  • Hello,

    We have designed a web ASP.NET 4.5 application Email system with a SQL Server 2008 R2 back end

    We want multiple users to access the same emails but have different views, particularly in the area of who read what email.

    If user A reads the first email we only want that email marked read for that user. So we set up a "user read" table.

    So in terms of table relationships, each an every user in the system (which will grow over time) has a table relationship with each and every email in the system (which can exponentially grow bigger). If this method keeps going, the "user read" will get really large really fast.

    Is there some better intermediary-hash-table-way to improve this design? If so how?


    Paul Mauriello

    Monday, December 2, 2013 3:13 PM

Answers

  • >Is there some better intermediary-hash-table-way way to improve this design? If so how?

    Probably not.  In the relational model, you just store the facts you want to store, here represented as (User,Email) tuples.

    They canonical physical design for a linking table in SQL Server would be like this (your keys and key types may vary):

    create table Users(UserId int primary key, ...)
    create table Emails(EmailId int primary key, ...)
    
    create table UserEmailRead
    (
      UserId int references Users on delete cascade,
      EmailId int references Emails on delete cascade,
      ReadDate datetime,
      constraint pk_UserEmailRead 
        primary key (UserId, EmailId)
    )
    create unique index uk_UserEmailRead
      on UserEmailRead(EmailID, UserId)

    Assuming you need to access the linking data from both the user side and the email side. 

    David


    David http://blogs.msdn.com/b/dbrowne/





    Monday, December 2, 2013 3:48 PM