title: Schema for notification system for users
body: <p>I'm writing an web application for users who need to be able to receive notices from administrators when they login. These notices should be displayed on their dashboard.</p>
  
  <p>Once a user has read a message, they can dismiss it. New users should obviously not be shown messages before they were created.</p>
  
  <p>I'm having two problems with designing the schema:</p>
  
  <ol>
  <li>How do I identify that a user has "dismissed" a message?</li>
  <li>How do new users only see messages after they've been created?</li>
  </ol>
  
  <p>Here's the table for notifications:</p>
  
  <pre><code>CREATE TABLE [dbo].[Notification] 
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Message] [varchar](max) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [CreatedById] [int] NOT NULL
  )
  </code></pre>
  
  <p>For #1, I was thinking of creating a table (<code>DismissedNotification_User</code>) that would map between a <code>Notification.Id</code> and a <code>User.Id</code>. If a pair exists, then the user dismissed that notification. However, I'm not sure if this is the best approach (<code>not in</code> vs a <code>left join</code>)? </p>
  
  <p>For #2, the easiest approach that I see is by adding a <code>DateCreated</code> column to users and when adding a condition onto #1 (<code>where [DateCreated] &gt;= [User].DateCreated</code>).</p>
  
  <p>I don't want cookies involved because that really adds unnecessary weight to the application.</p>