Tidbits on software development, technology, and other geeky stuff

CREATE TABLE Contact

From time to time I need to create a table to hold contact information. It seems like almost every (ok maybe not every) database has a table that contains information about contacts and/or users.  In an effort to reduce the time it takes write the DDL for this common table, here’s a simple CREATE statement (SQL Server syntax) to do the work.  Modify and execute to your heart’s content.

CREATE TABLE dbo.Contact (
  Contact_ID int NOT NULL IDENTITY (1, 1),
  First_Name varchar(50) NOT NULL,
  Middle_Name varchar(50) NULL,
  Last_Name varchar(50) NOT NULL,
  Name_Prefix varchar(8) NULL,
  Name_Suffix varchar(10) NULL,
  Address_1 varchar(50) NULL,
  Address_2 varchar(50) NULL,
  City varchar(50) NULL,
  State varchar(50) NULL,
  ZIP_Code varchar(10) NULL,
  Phone_Number varchar(15) NULL,
  Fax_Number varchar(15) NULL,
  Email_Address varchar(100) NULL,
  Social_Security_Number varchar(11) NULL,
  Date_Of_Birth date NULL,
  User_Name varchar(20) NULL,
  User_Password_Hash varchar(50) NULL,
  User_Password_Salt varchar(10) NULL,
  Created_Date datetime NOT NULL,
  Modified_Date datetime NOT NULL
)
GO
ALTER TABLE dbo.Contact ADD CONSTRAINT
  PK_Contact PRIMARY KEY CLUSTERED  (Contact_ID)
GO
ALTER TABLE dbo.Contact ADD CONSTRAINT
  DF_Contact_Created_Date DEFAULT GETUTCDATE() FOR Created_Date
GO
ALTER TABLE dbo.Contact ADD CONSTRAINT
  DF_Contact_Modified_Date DEFAULT GETUTCDATE() FOR Modified_Date
GO

Discuss on Twitter