Person.Person - AdventureWorks

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Person
Name	Person
Module	People

Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.

Columns

		Name	Data type	Attributes	References	Description
	1	BusinessEntityID	int		Person.BusinessEntity	Primary key for Person records.
Primary key for Person records. References: Person.BusinessEntity
	2	PersonType	nchar(2)			Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
	3	NameStyle	bit	Default: 0		0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. Default: 0
	4	Title	nvarchar(8)			A courtesy title. For example, Mr. or Ms.
A courtesy title. For example, Mr. or Ms.
	5	FirstName	nvarchar(50)			First name of the person.
First name of the person.
	6	MiddleName	nvarchar(50)			Middle name or middle initial of the person.
Middle name or middle initial of the person.
	7	LastName	nvarchar(50)			Last name of the person.
Last name of the person.
	8	Suffix	nvarchar(10)			Surname suffix. For example, Sr. or Jr.
Surname suffix. For example, Sr. or Jr.
	9	EmailPromotion	int	Default: 0		0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.
0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. Default: 0
	10	AdditionalContactInfo	xml			Additional contact information about the person stored in xml format.
Additional contact information about the person stored in xml format.
	11	Demographics	xml			Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
	12	rowguid	uniqueidentifier	Default: newid()		ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. Default: newid()
	13	ModifiedDate	datetime	Default: getdate()		Date and time the record was last updated.
Date and time the record was last updated. Default: getdate()

Relations

Foreign table	Primary table	Join	Title / Name / Description
Person.Person	Person.BusinessEntity	Person.Person.BusinessEntityID = Person.BusinessEntity.BusinessEntityID	FK_Person_BusinessEntity_BusinessEntityID Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Person.Person.BusinessEntityID = Person.BusinessEntity.BusinessEntityID Name: FK_Person_BusinessEntity_BusinessEntityID Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Person.BusinessEntityContact	Person.Person	Person.BusinessEntityContact.PersonID = Person.Person.BusinessEntityID	FK_BusinessEntityContact_Person_PersonID Foreign key constraint referencing Person.BusinessEntityID.
Person.BusinessEntityContact.PersonID = Person.Person.BusinessEntityID Name: FK_BusinessEntityContact_Person_PersonID Foreign key constraint referencing Person.BusinessEntityID.
Sales.Customer	Person.Person	Sales.Customer.PersonID = Person.Person.BusinessEntityID	FK_Customer_Person_PersonID Foreign key constraint referencing Person.BusinessEntityID.
Sales.Customer.PersonID = Person.Person.BusinessEntityID Name: FK_Customer_Person_PersonID Foreign key constraint referencing Person.BusinessEntityID.
Person.EmailAddress	Person.Person	Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID	FK_EmailAddress_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID Name: FK_EmailAddress_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
HumanResources.Employee	Person.Person	HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID	FK_Employee_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID Name: FK_Employee_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Person.Password	Person.Person	Person.Password.BusinessEntityID = Person.Person.BusinessEntityID	FK_Password_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Person.Password.BusinessEntityID = Person.Person.BusinessEntityID Name: FK_Password_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Sales.PersonCreditCard	Person.Person	Sales.PersonCreditCard.BusinessEntityID = Person.Person.BusinessEntityID	FK_PersonCreditCard_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Sales.PersonCreditCard.BusinessEntityID = Person.Person.BusinessEntityID Name: FK_PersonCreditCard_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Person.PersonPhone	Person.Person	Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID	FK_PersonPhone_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.
Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID Name: FK_PersonPhone_Person_BusinessEntityID Foreign key constraint referencing Person.BusinessEntityID.

Unique keys

	Key name	Columns	Description
	PK_Person_BusinessEntityID	BusinessEntityID	Primary key (clustered) constraint
Primary key (clustered) constraint
	AK_Person_rowguid	rowguid	Unique nonclustered index. Used to support replication samples.
Unique nonclustered index. Used to support replication samples.

Triggers

	Key name	When	Description
	iuPerson	After Insert, Update	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.
CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person] AFTER INSERT, UPDATE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics]) BEGIN UPDATE [Person].[Person] SET [Person].[Person].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> <TotalPurchaseYTD>0.00</TotalPurchaseYTD> </IndividualSurvey>' FROM inserted WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] AND inserted.[Demographics] IS NULL; UPDATE [Person].[Person] SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> as first into (/IndividualSurvey)[1]') FROM inserted WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] AND inserted.[Demographics] IS NOT NULL AND inserted.[Demographics].exist(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey/TotalPurchaseYTD') <> 1; END; END;
AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.
CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person] AFTER INSERT, UPDATE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics]) BEGIN UPDATE [Person].[Person] SET [Person].[Person].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> <TotalPurchaseYTD>0.00</TotalPurchaseYTD> </IndividualSurvey>' FROM inserted WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] AND inserted.[Demographics] IS NULL; UPDATE [Person].[Person] SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> as first into (/IndividualSurvey)[1]') FROM inserted WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] AND inserted.[Demographics] IS NOT NULL AND inserted.[Demographics].exist(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey/TotalPurchaseYTD') <> 1; END; END;

Uses

Name
Person.Person
Person.BusinessEntity
Person.iuPerson
Person.Person

Used by

Name
Person.Person
HumanResources.vEmployee
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory
Person.vAdditionalContactInfo
Purchasing.vVendorWithContacts
Sales.vIndividualCustomer
Sales.vPersonDemographics
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears
Sales.vStoreWithContacts
dbo.uspGetEmployeeManagers
dbo.uspGetManagerEmployees
dbo.ufnGetContactInformation
Person.iuPerson
Sales.iduSalesOrderDetail
HumanResources.Employee
Person.BusinessEntityContact
Person.EmailAddress
Person.Password
Person.PersonPhone
Sales.Customer
Sales.PersonCreditCard

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56