Database Design

There are 3 tables in the design of the database:

Convo

Column Name	Type	Identity?	Nullable?	Default Value
[Id]	BIGINT	YES, Seed = 1, Increment = 1	NO
[Creator]	BIGINT	NO	NO
[DateCreated]	DATETIME	NO	NO
[DateDeleted]	DATETIME	NO	YES	NULL
[DateOfLastMessage]	DATETIME	NO	YES	NULL
[DateUpdated]	DATETIME	NO	NO
[IsDeleted]	BIT	NO	NO	0
[Participant]	BIGINT	NO	NO
[Subject]	NVARCHAR(140)	NO	YES	NULL

The [Id] Column is the primary key for this table.

The [Creator] column holds the user_id of the user who created this Convo. This column references the User table with a foreign key constraint.

The [DateCreated] column holds the date that the Convo was created.

The [DateDeleted] column holds the date that the Convo was deleted.

The [DateOfLastMessage] column holds the date that the most recent message in the Convo was created. This is nullable because a Convo may be created without any messages.

The [DateUpdated] column holds the date that the Convo was updated (for example, if the [Subject] of the Convo was updated).

The [IsDeleted] column indicates whether or not the Convo was deleted. Convos are never actually deleted from the system. They are only marked as having been deleted.

The [Participant] column holds the user Id of the user with whom the conversation is occurring (other than the creator). This column references the User table with a foreign key constraint.

The [Subject] column holds the subject of the Convo. This field has a limit of 140 characters.

Message

Column Name	Type	Identity?	Nullable?	Default Value
[Id]	BIGINT	YES, Seed = 1, Increment = 1	NO
[Body]	NVARCHAR(MAX)	NO	YES
[ConvoId]	BIGINT	NO	NO
[DateCreated]	DATETIME	NO	NO
[DateDeleted]	DATETIME	NO	YES	NULL
[DateUpdated]	DATETIME	NO	NO
[IsDeleted]	BIT	NO	NO	0
[IsRead]	BIT	NO	NO	0
[Parent]	BIGINT	NO	YES	NULL
[Recipient]	BIGINT	NO	NO
[Sender]	BIGINT	NO	NO

The [Id] Column is the primary key for this table.

The [Body] column holds the body of the message. There is no database restriction on size because of SQL Server limitations (NVARCHAR can only be limited up until 4000 characters. Beyond that it must be declared as MAX). The application handles validation of character limits.

The [ConvoId] column holds the Id of the Convo to which the message belongs. This column references the Convo table with a foreign key constraint.

The [DateCreated] column holds the date that the Message was created.

The [DateDeleted] column holds the date that the Message was deleted.

The [DateUpdated] column holds the date that the Message was updated (for example, if the [Body] of the Convo was updated).

The [IsDeleted] column indicates whether or not the Message was deleted. Messages are never actually deleted from the system. They are only marked as having been deleted.

The [IsRead] column indicates whether or not the Message was read.

The [Parent] column holds the id of the Message to which the current Message is in reply to. This column references the Message table with a foreign key constraint.

The [Recipient] column holds the user id of the user who will receive the message. This column references the User table with a foreign key constraint.

The [Sender] column holds the user id of the user who sent the message. This column references the User table with a foreign key constraint.

User

Column Name	Type	Identity?	Nullable?	Default Value
[user_id]	BIGINT	NO	NO
[FirstName]	NVARCHAR(100)	NO	NO
[LastName]	NVARCHAR(100)	NO	NO

This table is primarily for referential integrity purposes. There is no updates, inserts or deletes performed on this table from the application.