Explain BusinessEntity tables in AdventureWorks2008R2

Question

I'm designing my first enterprise level sql server database and want to use AdventureWorks2008R2 as a concept model to copy some of what it demonstrates re good design. But I'm struggling to understand how the BusinessEntity tables get populated.

Where is there documentation that explains how the BusinessEntity tables work?

Is there sample front end code that would demonstrate how the tables get populated etc? e.g. if the app adds a new employee/person what is the code for populating the BusinessEntity table. Is there code somewhere that demonstrates this?

I'm using LINQ to SQL for this project in case thats relevant.

Answer 1

A business entity is a person or business which has zero or more addresses and zero or more people related to it. For example, the people related to a store would typically be purchasing agents of a retail sporting or bicycle store who buy bicycles wholesale from AdventureWorks Cycles. A person related to an employee might be the employee’s emergency contact. All business entities have a common key (the BusinessEntityID). This allows us to build a conceptual model using the Entity Framework which demonstrates table per type inheritance. It also simplifies the schema by having a single table (Person.BusinessEntityAddress) relate addresses to business entities instead of using specialized tables (for example CustomerAddress, VendorAddress, EmployeeAddress.

The concept of a customer has also been broadened. In AdventureWorks for SQL Server 2005, a customer is either a store (wholesale) or an individual (retail). In AdventureWorks2008R2 and 2012, a customer can be any person or a store. This enables employees (for example) to be customers without storing redundant information about them.

A Customer can be a Person or a Store.

For Example:

USE AdventureWorks2012

GO

-- Customer as a Person. Jon Yang is the customer

SELECT

[PersonID]

,[CustomerID]

,[StoreID]

FROM [AdventureWorks2012].[Sales].[Customer]

WHERE CustomerID = 11000

GO

-- The BusinessEntityID represents the Person

SELECT

[BusinessEntityID]

,[FirstName]

,[LastName]

FROM [AdventureWorks2012].[Person].[Person]

WHERE [BusinessEntityID] = 13531

GO

-- Customer as a Store. A Bike Store is the customer

SELECT

[StoreID]

,[CustomerID]

,[PersonID]

FROM [AdventureWorks2012].[Sales].[Customer]

WHERE CustomerID = 1

GO

-- The BusinessEntityID represents the Store

SELECT

[BusinessEntityID]

,[Name]

FROM [AdventureWorks2012].[Sales].[Store]

WHERE BusinessEntityID = 934

GO

Also Answers: http://social.msdn.microsoft.com/Forums/en-US/sqlserversamples/thread/dfc1c1f6-cf33-491e-aec1-5803cb406572