AdventureWorks2008R2 - What is the relationship between the Customer, Store and Business Entity tables and how records are created?

Question

What is the relationship between the Customer, Store and Business Entity tables and how records are created?

Are customers in Customer table customers of a particular/single store in Store table?

Are in turn stores (Store table), customers of Adventure Works cycle manufacturer, that is Adventure Works sell cycles to stores and stores sell them to customers?

So customers in Customer table are not "direct" customers to Adventure Works but "indirect customers" that is customers through the stores, right?

What is the exact meaning of each table?

Answer 1

Did you end up finding an answer to your question? If so, could you please provide the solution.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 2

Bumping to top of question list

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 3

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