Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
320 lines (271 sloc) 11.7 KB
Reserved Word Checkers:
http://www.petefreitag.com/tools/sql_reserved_words_checker/
http://www.reservedwordsearch.com/
Database Standard Operating Procedures
- Use First Char Uppercase Naming (for Linq)
- Use singular naming for tables: Account NOT Accounts
[http://stackoverflow.com/a/5841297/665387]
** Use plural naming for Lookup Tables Only: Roles Not Role
- Use underscore for many-to-many relationship tables
User (no 's') and Roles (with 's' lookup) it would be User_Role
with the "dominant" table taking place on the left.
[http://stackoverflow.com/a/631859/665387]
- Do not use the word 'date' : created Not createdDate
Both id and guid in tables
id = internal use for queries
guid = exteral use for querystrings, api calls
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
** Dont use MONEY ***
[FaceValue] DECIMAL(19,4) NOT NULL DEFAULT ((0)),
DateTime: (getdate())
GUID: (newid())
CREATE TABLE [dbo].[account](
[Id] [int] identity(1,1) primary key not null,
[Guid] [uniqueidentifier] not null default ((newid())),
[LastName] [nvarchar](50) NOT NULL default ('0'),
[FirstName] [nvarchar](50) NOT NULL default ('0'),
[Email] [nvarchar](200) not null default ('0'),
[Password] [nvarchar](max) NOT NULL,
[PasswordSalt] [nvarchar](max) NOT NULL,
[Administrator] [bit] not null default ((0))
[LastLogin] [datetime] NULL,
[Created] [datetime] not null default (getdate()),
[Modified] [datetime] not null default (getdate()),
[Active] [bit] not null default ((1))
)
create table [dbo].[hr_app_employment](
[Id] [int] identity(1,1) primary key not null,
[Guid] [uniqueidentifier] not null default ((newid())),
[Title] [nvarchar](150) not null default ('0'),
[Email] [nvarchar](200) not null default ('0'),
[Password] [nvarchar](max) not null default ('0'),
[Passwordsalt] [nvarchar](max) not null default ('0'),
[Ismoderator] [bit] not null default ((0)),
[Isadministrator] [bit] not null default ((0)),
[Lastlogin] [datetime] null,
[Created] [datetime] not null default (getdate()),
[Modified] [datetime] not null default (getdate()),
[Sort] [int] not null default ((0)),
[Active] [bit] not null default ((1))
)
ok
CREATE TABLE [dbo].[exceptionLog](
[Id] [int] identity(1,1) not null primary key,
[ExceptionStack] [nvarchar](max) not null default ('0'),
[ErrorState] [nvarchar](50) not null default ('0'),
[CustomInfo] [nvarchar](max) null,
[Created] [datetime] not null default (getdate()),
[ExceptionMessage] [nvarchar](max) not null default ('0'),
[ExceptionSource] [nvarchar](max) not null default ('0'),
[ExceptionTargetsite] [nvarchar](max) not null default ('0'),
[ExceptionData] [nvarchar](max) null
)
create table [dbo].[Customer](
[Id] [int] identity(1,1) primary key not null,
[Guid] [uniqueidentifier] not null default ((newid())),
[ClientId] [int] NOT NULL default (0),
[Name] [nvarchar](150) NOT NULL default('0'),
[Address1] [nvarchar](150) NOT NULL default ('0'),
[Address2] [nvarchar](150) NOT NULL default ('0'),
[City] [nvarchar](150) NOT NULL default ('0'),
[State] [nvarchar](2) NOT NULL default ('0'),
[Zip] [nvarchar](10) NOT NULL default ('0'),
[Modified] [datetime] NOT NULL default (getdate()),
[Active] [bit] NOT NULL default ((1)),
)
create table [dbo].[CustomerContact]
(
[Id] [int] identity(1,1) primary key not null,
[Guid] [uniqueidentifier] not null default ((newid())),
[CustomerId] [int] NOT NULL default (0),
[LastName] [nvarchar](50) NOT NULL default ('0'),
[FirstName] [nvarchar](50) NOT NULL default ('0'),
[MiddleName] [nvarchar](50) NOT NULL default ('0'),
[Address1] [nvarchar](150) NOT NULL default ('0'),
[Address2] [nvarchar](150) NOT NULL default ('0'),
[City] [nvarchar](150) NOT NULL default ('0'),
[State] [nvarchar](2) NOT NULL default ('0'),
[Zip] [nvarchar](10) NOT NULL default ('0'),
[Phone] [nvarchar](10) NOT NULL default ('0'),
[Cell] [nvarchar](10) NOT NULL default ('0'),
[Email] [nvarchar](200) not null default ('0'),
[Modified] [datetime] NOT NULL default (getdate()),
[Active] [bit] NOT NULL default ((1)),
)
CREATE TABLE [dbo].[product](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[categoryId] [int] not null default (0),
[title] [nvarchar](50) NOT NULL default ('0'),
[sku] [nvarchar](50) NOT NULL default ('0'),
[description] [nvarchar](max) NOT NULL default ('0'),
[descriptionShort] [nvarchar](200) NOT NULL default ('0'),
[features] [nvarchar](max) NOT NULL default ('0'),
[quickstart] [nvarchar](max) NOT NULL default ('0'),
[usermanual] [nvarchar](max) NOT NULL default ('0'),
[specsheet] [nvarchar](max) NOT NULL default ('0'),
[highlights] [nvarchar](max) NOT NULL default ('0'),
[imageFilename] [nvarchar](max) NOT NULL default ('0'),
[imageFilenameZoom] [nvarchar](max) NOT NULL default ('0'),
[imageFileNameCategory] [nvarchar](max) NOT NULL default ('0'),
[created] [datetime] not null default (getdate()),
[modified] [datetime] not null default (getdate()),
[sort] [int] not null default ((0)),
[active] [bit] not null default ((1))
)
create table [dbo].[image](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[title] [nvarchar](150) not null default ('0'),
[sku] [nvarchar](50) NOT NULL default ('0'),
[filename] [nvarchar](200) not null default ('0'),
[filenameThumbnail] [nvarchar](200) not null default ('0'),
[filenameZoom] [nvarchar](max) NOT NULL default ('0'),
[isYouTube] [bit] not null default ((0)),
[youtubeUrl] [nvarchar](200) not null default ('0'),
[created] [datetime] not null default (getdate()),
[modified] [datetime] not null default (getdate()),
[sort] [int] not null default ((0)),
[active] [bit] not null default ((1))
)
create table [dbo].[notes](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[accountId] [int] NOT NULL default (0),
[recordId] [int] NOT NULL default (0),
[recordTable] [nvarchar] (150) not null default ('0'),
[memo] [nvarchar](max) NOT NULL default ('0'),
[created] [datetime] NOT NULL default (getdate()),
[ipAddress] [nvarchar](50) NOT NULL default ('0'),
)
create table [dbo].[exceptionLog](
[id] [int] identity(1,1) primary key not null,
[exceptionStack] [nvarchar](max) NOT NULL default ('0'),
[errorState] [nvarchar](50) NOT NULL default ('0'),
[customInfo] [nvarchar](max) NULL default ('0'),
[created] [datetime] NOT NULL default (getdate()),
[exceptionMessage] [nvarchar](max) NOT NULL default ('0'),
[exceptionSource] [nvarchar](max) NOT NULL default ('0'),
[exceptionTargetsite] [nvarchar](max) NOT NULL default ('0'),
[exceptionData] [nvarchar](max) NULL default ('0'),
)
create table [dbo].[trackingLog](
[id] [int] identity(1,1) primary key not null,
[created] [datetime] NOT NULL default (getdate()),
[clientId] [int] NOT NULL default (0),
[memberId] [int] NOT NULL default (0),
[recordId] [int] NOT NULL default (0),
[recordTable] [nvarchar] (150) not null default ('0'),
[memo] [nvarchar](max) NOT NULL default ('0'),
[ipAddress] [nvarchar](50) NOT NULL default ('0'),
[trackingState] [int] NOT NULL default (0),
)
create table [dbo].[roles](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[title] [nvarchar](150) NOT NULL default ('0'),
[description] [nvarchar](max) NOT NULL default ('0'),
[active] [bit] NOT NULL default ((1)),
[alert] [bit] NOT NULL default ((0)),
)
create table [dbo].[account](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[lastName] [nvarchar](50) NOT NULL default ('0'),
[firstName] [nvarchar](50) NOT NULL default ('0'),
[email] [nvarchar] (200) not null default ('0'),
[password] [nvarchar] (max) not null default ('0'),
[passwordSalt] [nvarchar] (max) not null default ('0'),
[clientId] [int] NOT NULL default (0),
[administrator] [bit] NOT NULL default ((1)),
[lastLogin] [datetime] NULL,
[created] [datetime] not null default (getdate()),
[modified] [datetime] not null default (getdate()),
[active] [bit] NOT NULL default ((1)),
)
create table [dbo].[account_role](
[id] [int] identity(1,1) primary key not null,
[clientId] [int] NOT NULL default (0),
[accountId] [int] NOT NULL default (0),
[roleId] [int] NOT NULL default (0),
[created] [datetime] NOT NULL default (getdate()),
[modified] [datetime] NOT NULL default (getdate()),
[active] [bit] NOT NULL default ((1)),
)
//------------------------------------------------------------//
create table [dbo].[contact](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[clientId] [int] NOT NULL default (0),
[cemeteryId] [int] NOT NULL default (0),
[lastName] [nvarchar](50) NOT NULL default ('0'),
[firstName] [nvarchar](50) NOT NULL default ('0'),
[middleName] [nvarchar](50) NOT NULL default ('0'),
[address1] [nvarchar](150) NOT NULL default ('0'),
[address2] [nvarchar](150) NOT NULL default ('0'),
[city] [nvarchar](150) NOT NULL default ('0'),
[state] [nvarchar](2) NOT NULL default ('0'),
[zip] [nvarchar](10) NOT NULL default ('0'),
[phone] [nvarchar](10) NOT NULL default ('0'),
[cell] [nvarchar](10) NOT NULL default ('0'),
[email] [nvarchar](200) not null default ('0'),
[created] [datetime] NOT NULL default (getdate()),
[modified] [datetime] NOT NULL default (getdate()),
[active] [bit] NOT NULL default ((1)),
)
create table [dbo].[contact_type](
[id] [int] identity(1,1) primary key not null,
[contactId] [int] NOT NULL default (0),
[contactTypeId] [int] NOT NULL default (0),
[created] [datetime] NOT NULL default (getdate()),
[modified] [datetime] NOT NULL default (getdate()),
[active] [bit] NOT NULL default ((1)),
)
create table [dbo].[contactTypes](
[id] [int] identity(1,1) primary key not null,
[title] [nvarchar](150) NOT NULL default ('0'),
[description] [nvarchar](max) NOT NULL default ('0'),
[active] [bit] NOT NULL default ((1)),
[alert] [bit] NOT NULL default ((0)),
)
create table [dbo].[owner](
[id] [int] identity(1,1) primary key not null,
[guid] [uniqueidentifier] not null default ((newid())),
[clientId] [int] NOT NULL default (0),
[lastName] [nvarchar](50) NOT NULL default ('0'),
[firstName] [nvarchar](50) NOT NULL default ('0'),
[middleName] [nvarchar](50) NOT NULL default ('0'),
[address1] [nvarchar](150) NOT NULL default ('0'),
[address2] [nvarchar](150) NOT NULL default ('0'),
[city] [nvarchar](150) NOT NULL default ('0'),
[state] [nvarchar](2) NOT NULL default ('0'),
[zip] [nvarchar](10) NOT NULL default ('0'),
[phone] [nvarchar](10) NOT NULL default ('0'),
[cell] [nvarchar](10) NOT NULL default ('0'),
[email] [nvarchar](200) not null default ('0'),
[created] [datetime] NOT NULL default (getdate()),
[modified] [datetime] NOT NULL default (getdate()),
[active] [bit] NOT NULL default ((1)),
)
create table [dbo].[EmailQueue](
[id] [int] identity(1,1) primary key not null,
[batchId] [int] NOT NULL default (0),
[created] [datetime] not null default (getdate()),
[toEmail] [nvarchar](200) not null default ('0'),
[fromEmail] [nvarchar](200) not null default ('0'),
[completed] [bit] not null default ((1))
)
create table [dbo].[EmailQueueBody](
[id] [int] identity(1,1) primary key not null,
[batchId] [int] NOT NULL default (0),
[created] [datetime] not null default (getdate()),
[body] [nvarchar](max) NOT NULL default ('0'),
[subject] [nvarchar] (150) not null default ('0'),
[attachment1] [nvarchar](200) null,
[attachment2] [nvarchar](200) null,
)
Referential integrity > relationships
ALTER TABLE memberContact ADD CONSTRAINT FK_memberContact_member
FOREIGN KEY(memberId)
REFERENCES Member(id)