MarketingSurveyFormDBLayout

10/02/2009 4:34 PM

Database Layout

Tables

tblCompanies

NameTypeNULLindex
CompanyNamenvarchar(255)
PK
DateStampdatetime

tblJobNums

NameTypeNULLindex
JobNumvarchar(50)
PK
DateStampdatetime

tblSurveyPosts

NameTypeNULLindex
GUIDnvarchar(64)
keynvarchar(255)
valuenvarchar(255)x
timestampdatetime

tblSurveys

NameTypeNULLindex
SurveyIDint (AUTO)
PK
SurveyNamenvarchar(255)
SurveyTimestampdatetime

tblSurveysSent

NameTypeNULLindex
GUIDnvarchar(64)
SurveyIDint
Timestampdatetime
emailnvarchar(255)
namenvarchar(255)x
productnamenvarchar(255)x
jobnumvarchar(12)x
clientCompanynvarchar(255)x
fromemailnvarchar(255)x
emailSubjectnvarchar(1023)x
emailmessagetextx

Views

vw_Corespondance

SELECT     email AS ClientEmail, name AS ClientName, productname AS ProductName, jobnum AS JobNumber,                        clientCompany AS ClientCompany, fromemail AS StaffEmail, CASE WHEN EXISTS                           (SELECT     1                             FROM          tblSurveyPosts                             WHERE      tblSurveyPosts.GUID = tblSurveysSent.GUID) THEN 1 ELSE 0 END AS HasResponded FROM         dbo.tblSurveysSent 

vw_Parameters

SELECT     TOP 100 PERCENT dbo.sysobjects.name AS SPname, dbo.syscolumns.name, dbo.systypes.name AS type,                        dbo.syscolumns.length AS size, dbo.syscolumns.isoutparam FROM         dbo.sysobjects INNER JOIN                       dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN                       dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype AND dbo.systypes.name <> 'sysname' WHERE     (dbo.sysobjects.xtype = 'P') OR                       (dbo.sysobjects.xtype = 'U') 

vw_aggregates

SELECT     dbo.tblSurveys.SurveyName, dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], COUNT(*) AS number FROM         dbo.tblSurveysSent INNER JOIN                       dbo.tblSurveys ON dbo.tblSurveysSent.SurveyID = dbo.tblSurveys.SurveyID INNER JOIN                       dbo.tblSurveyPosts ON dbo.tblSurveysSent.GUID = dbo.tblSurveyPosts.GUID GROUP BY dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], dbo.tblSurveysSent.SurveyID, dbo.tblSurveys.SurveyName 

vw_responces

SELECT     dbo.tblSurveys.SurveyName, dbo.tblSurveysSent.email AS ClientEmail, dbo.tblSurveysSent.name AS ClientName,                        dbo.tblSurveysSent.productname AS ProductName, dbo.tblSurveysSent.jobnum AS JobNum,                         dbo.tblSurveysSent.clientCompany AS ClientCompany, dbo.tblSurveysSent.fromemail AS StaffEmail,                        dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], dbo.tblSurveyPosts.[timestamp] FROM         dbo.tblSurveyPosts INNER JOIN                       dbo.tblSurveysSent ON dbo.tblSurveyPosts.GUID = dbo.tblSurveysSent.GUID INNER JOIN                       dbo.tblSurveys ON dbo.tblSurveysSent.SurveyID = dbo.tblSurveys.SurveyID 
Tags:
Home: WikiStart
What's new: Recently changed articles
You can subscribe to this wiki article using an RSS feed reader.