Example Database Schema: Typical WebSite

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(100,1) NOT NULL,
	[ContactId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[LoginId] [char](32)  NOT NULL,
	[Password] [char](64)
);

CREATE TABLE [dbo].[Roles](
	[RoleId] [int] IDENTITY(100,1) NOT NULL,
	[Name] [char](32) NOT NULL,
	[Description] [char](256) NOT NULL,
);

CREATE TABLE [dbo].[Contacts](
	[ContactId] [int] IDENTITY(1001,1) NOT NULL,
	[LoginId] [char](32)  NOT NULL,
	[FirstName] [char](30) NULL,
	[LastName] [char](30) NULL,
	[StreetAddress] [char](60) NULL,
	[ApptNo] [char](10) NULL,
	[City] [char](30) NULL,
	[State] [char](2) NULL,
	[Zip] [char](10) NULL,
	[Country] [char](2) NULL,
	[PhoneHome] [char](12) NULL,
	[PhoneWork] [char](12) NULL,
	[PhoneCell] [char](12) NULL,
	[Email1] [char](256) NULL,
	[Email2] [char](256) NULL
);

ALTER TABLE [dbo].[Users] ADD PRIMARY KEY (UserId);
ALTER TABLE [dbo].[Roles] ADD PRIMARY KEY (RoleId);
ALTER TABLE [dbo].[Contacts] ADD PRIMARY KEY (ContactId);

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Contacts] FOREIGN KEY([ContactId]) REFERENCES [dbo].[Contacts] ([ContactId]);
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Roles] FOREIGN KEY([RoleId]) REFERENCES [dbo].[Roles] ([RoleId]);

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

AddThis Social Bookmark Button

Similar Posts

Comments are closed.