none
Explain BusinessEntity tables in AdventureWorks2008R2

    Pregunta

  • 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.

    viernes, 11 de marzo de 2011 14:27

Respuestas

  • 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

    miércoles, 19 de septiembre de 2012 19:56
    Moderador

Todas las respuestas

  • 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

    miércoles, 19 de septiembre de 2012 19:56
    Moderador
  • Hi ,

    I am just trying to learn sql using the adventureowrks2012 database . I just came across the Sales.Customer table where there are values filled for both the PersonID and the StoreID. Is it possible ?When the customer can either be a store or a person can there be values for both the columns at the same time. Please reply

    Thanks

    viernes, 07 de febrero de 2014 21:03