Connacht Database Tables

Connacht is a multi-user, multi-company web site. As a result it is necessary to ensure that clients of one company cannot be seen by employees or officers of another company. This is done by storing a unique company ID with both the user and each client record - the user is only allowed to see a client record if he or she is an employee or officer of the same company that owns the client record. (The current implementation only allows a user to be a member of one company)

Connacht implements client record versioning. Each time the client record is updated, the version number on the record is updated. If two users attempt to update the same client details at the same time, only one will succeed. The second user will receive a message saying that client details have been updated by another user and  his data is now out-of-date. The second user must then refresh the webpage and redo his changes before they can be resubmitted.

The alternative is that a second user overwrites the first users changes without being aware of it. The first user would almost certainly attribute this behaviour to a bug in the application.

No versioning is implemented on the User table - a User should correspond to a single person so it is unlikely that a single user would logon twice and then proceed to update his details twice. 

Client Table

SQL Create Table Statement

CREATE TABLE [Clients] 
(
	[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
	[Version] [int] NOT NULL ,
	[CompanyID] [int] NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_Clients] PRIMARY KEY  CLUSTERED <
	(
		[ClientID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Clients] UNIQUE  NONCLUSTERED
	(
		[Name]
	)  ON [PRIMARY]
) ON [PRIMARY]

Fields

ClientID - A unique client ID generated by the database when the record is first inserted, currently an integer (32bits).
Version - The version number used to detect updates based on out-of-date information. The version number starts at 0 and is incremented every time the client record is updated.
CompanyID - a unique ID specifying which company "owns" the record. 
Name - the clients name.
Address1 - the first line of the user's address.
Address2 - the second line of the user's address.
PostCode - the clients post code.
email - the client's email address. Optional (field can be null)
Telephone - the client's telephone number. Optional (field can be null)

User Table

SQL Create Table Statement

CREATE TABLE [Users] 
(
	[Login] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyID] [int] NOT NULL ,
	[Title] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Surname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[login]
	)  ON [PRIMARY] 
) ON [PRIMARY]

Fields

Login - the user's first name. 
CompanyID - a unique company ID, currently an integer (32 bits).
FirstName - the user's first name. 
Surname - the user's surname. 
Address1 - the first line of the user's address.
Address2 - the second line of the user's address.
PostCode - the user's post code.
email - the user's email address.  Optional (field can be null)
Telephone - the user's telephone number. Optional (field can be null)
Password - the user's password.