Sales.Individual Table

Description

Demographic data about customers that purchase Adventure Works products online.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	18484
Data Space Used	24,648.00 KB
Index Space Used	56.00 KB

Columns

Column Name	Description	Datatype	Length	Default
CustomerID	Unique customer identification number. Foreign key to Customer.CustomerID.	int	4
ContactID	Identifies the customer in the Contact table. Foreign key to Contact.ContactID.	int	4
Demographics	Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.	Sales.IndividualSurveySchemaCollection	16
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
PK_Individual_CustomerID	Primary key (clustered) constraint

Triggers

Trigger	Description
iuIndividual	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.

Relationships

Relationship	Description
FK_Individual_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_Individual_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.

Objects that depend on Sales.Individual

Database Object	Object Type	Description	Dep Level
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	2
iStore	Trigger	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.	2
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	2
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	1
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	2

Objects that Sales.Individual depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	3
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		4
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Individual](
	[CustomerID] [int] NOT NULL,
	[ContactID] [int] NOT NULL,
	[Demographics] [xml](CONTENT [Sales].[IndividualSurveySchemaCollection]) NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Individual_CustomerID] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Sales].[Individual]  WITH CHECK ADD  CONSTRAINT [FK_Individual_Contact_ContactID] FOREIGN KEY([ContactID])
REFERENCES [Contact] ([ContactID])
ALTER TABLE [Sales].[Individual] CHECK CONSTRAINT [FK_Individual_Contact_ContactID]
ALTER TABLE [Sales].[Individual]  WITH CHECK ADD  CONSTRAINT [FK_Individual_Customer_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [Customer] ([CustomerID])
ALTER TABLE [Sales].[Individual] CHECK CONSTRAINT [FK_Individual_Customer_CustomerID]
ALTER TABLE [Sales].[Individual] ADD  CONSTRAINT [DF_Individual_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Sales Schema
AdventureWorks Database