Database Design for an Internal Admin Chat System

Question

Hi, I'm trying to implement an internal chat system for our admins to use, but I'm not too sure on the database design. I have this (below) so far, but would appreciate a verification and/or recommendation for improvement.

Employees {
    EmployeeId (smallint)
    // ...
}

Chat {
    ChatId (int)
    Stamp (datetime) // Obsolete, ignore...
}

ChatEmployees {
    ChatEmployeeId (int) // Or bigint?
    ChatId (int) -> Chat.ChatId
    EmployeeId (smallint) -> Employees.EmployeeId
}

Messages {
    MessageId (int) // Or bigint?
    AuthorId (smallint) -> Employees.EmployeeId
    ChatId (int) -> Chat.ChatId
    Text (varchar(512))
    Stamp (datetime)
}

So, that's what I have so far, but I'm not sure if it's "sufficient". The application that will be interacting with the database is built with ASP.NET MVC 2 and Linq to SQL.

Thanks in advance for any suggestions!

Answer 1

The ChatEmployees table seems kind of redundant. Unless you need to record that someone was in a chat but never said anything I'd drop it.

Re: Message.text
I think "text" might be a reserved word as it is used as a datatype in SQL. Might want to avoid that name.

Also, varchar 512 seems like kind of an arbitrary number of characters to allow for a message, why the odd number? Are you picking it just because it is a power of 2 for some reason?