Person.BusinessEntity Table

Description

Source of the ID that connects vendors, customers, and employees with address and contact information.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	20777
Data Space Used	768.00 KB
Index Space Used	568.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Primary key for all customers, vendors, and employees.	Integer	4
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_BusinessEntity_rowguid	Unique nonclustered index. Used to support replication samples.
PK_BusinessEntity_BusinessEntityID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.
FK_BusinessEntityContact_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.
FK_Person_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.
FK_Store_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID
FK_Vendor_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID

Objects that depend on Person.BusinessEntity

Database Object	Object Type	Description	Dep Level
Person.BusinessEntityAddress	Table	Cross-reference table mapping customers, vendors, and employees to their addresses.	1
Person.BusinessEntityContact	Table	Cross-reference table mapping stores, vendors, and employees to people	1
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	2
dEmployee	Trigger	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	3
Production.Document	Table	Product maintenance documents.	3
dVendor	Trigger	INSTEAD OF DELETE trigger which keeps Vendors from being deleted.	2
Person.EmailAddress	Table	Where to send a person email.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	2
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	3
HumanResources.EmployeePayHistory	Table	Employee pay history.	3
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.	3
iuPerson	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 Person table to the current date.	2
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	3
Person.Password	Table	One way hashed authentication information	2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
Sales.PersonCreditCard	Table	Cross-reference table mapping people to their credit card information in the CreditCard table.	2
Person.PersonPhone	Table	Telephone number and type of a person.	2
Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	4
Purchasing.ProductVendor	Table	Vendors are added to the table before any POs are processed.	2
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesPerson	Table	Sales representative current information.	3
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	4
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	4
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	2
dbo.uspGetEmployeeManagers	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.	2
dbo.uspGetManagerEmployees	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.	2
dbo.uspSearchCandidateResumes	Stored Procedure		4
HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	3
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.	3
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	3
Person.vAdditionalContactInfo	View	Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.	2
HumanResources.vEmployee	View	Employee names and addresses.	2
HumanResources.vEmployeeDepartment	View	Returns employee name, title, and current department.	2
HumanResources.vEmployeeDepartmentHistory	View	Returns employee name and current and previous departments.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	2
HumanResources.vJobCandidate	View	Job candidate names and resumes.	4
HumanResources.vJobCandidateEducation	View	Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	4
HumanResources.vJobCandidateEmployment	View	Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	4
Sales.vPersonDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.	2
Sales.vSalesPerson	View	Sales representiatives (names and addresses) and their sales-related information.	2
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	2
Sales.vStoreWithAddresses	View	Stores (including store addresses) that sell Adventure Works Cycles products to consumers.	2
Sales.vStoreWithContacts	View	Stores (including store contacts) that sell Adventure Works Cycles products to consumers.	2
Sales.vStoreWithDemographics	View	Stores (including demographics) that sell Adventure Works Cycles products to consumers.	2
Purchasing.vVendorWithAddresses	View	Vendor (company) names and addresses .	2
Purchasing.vVendorWithContacts	View	Vendor (company) names and the names of vendor employees to contact.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[BusinessEntity](
    [BusinessEntityID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BusinessEntity_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] 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 [Person].[BusinessEntity] ADD  CONSTRAINT [DF_BusinessEntity_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[BusinessEntity] ADD  CONSTRAINT [DF_BusinessEntity_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Person Schema
AdventureWorks Database