locked
Adventure works database sample question RRS feed

  • Question

  • Dear all,

    I need to build a database structure for a shop which is actually selling home furniture and I have seen that quite many table from the AW database could fit my scenario but I have some points that I do not catch in that sample.

    If anyone could light my brain I will appreciate.

    1- First of all in many tables I can see a reference to a BusinessEntityID but could find any table where BusinessEntity is define ?

    2- In my scenario my company will have full control on the database and will resell the software to shops. Shops can be a single shop of belonging to a HeadOffice which is the owner of those shops. The following picture illustrate this

    - Shops Owner 1 to 3 are customers of My COMPANY
    - Shop 1 to Shop 3 are customers of respective ShopOwner

    How can I define and identify those entity in AW sample database ?

    Thnaks for help

    regards

    Tuesday, February 18, 2014 9:43 AM

Answers

All replies

  • Hello,

    Regarding 1: It's from schema "Person", table "BusinessEntity":


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, February 18, 2014 9:52 AM
    Answerer
  • Hello it seems I do not have the same shema as yours. May be I do not have the correct adventure works database ?

    My table for person is called Person.Person , may be I have a more complex structure ?

    regards

    Tuesday, February 18, 2014 10:34 AM
  • AdventureWorks uses several schemas and in schema "Person" you should also find the table "BusinessEntity".

    You can get a current version of AdventureWorks form CodePlex: http://msftdbprodsamples.codeplex.com/


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, February 18, 2014 11:01 AM
    Answerer
  • So does a BusinessEntiy is nothing else than a CUSTOMER in fact ?

    IN orther word does each of my blue box in my picture is a BusinessEntity = a Customer?

    May be it is just a matter of naming but need to catch it correctly :-)
    • Edited by wakefun Tuesday, February 18, 2014 11:11 AM
    Tuesday, February 18, 2014 11:09 AM
  • So does a BusinessEntiy is nothing else than a CUSTOMER in fact ?

    IN orther word does each of my blue box in my picture is a BusinessEntity = a Customer?

    May be it is just a matter of naming but need to catch it correctly :-)

    Hi wakefun,

    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.

    In AdventureWorks2008R2 and 2012, the concept of a customer has also been broadened, a customer can be any person or a store. This enables employees (for example) to be customers without storing redundant information about them.

    For more information about BusinessEntity tables in AdventureWorks2008R2, you can review the following post.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dfc1c1f6-cf33-491e-aec1-5803cb406572/explain-businessentity-tables-in-adventureworks2008r2?forum=sqlserversamples

    Regards,
    Sofiya Li



    Sofiya Li
    TechNet Community Support

    Wednesday, February 19, 2014 9:52 AM
  • Thanks more clear now.

    So if I am coming back to my scenario:
    - customers of "MY COMPANY" box are in fact the Shops Owner's

    - customers of SopsOwners are individual shops which are manage by their respective own Director

    - Shops customers will be then Individual customers.

    So "MY COMPANY" can have One or Many ShopsOwner and ShopsOnwers can have One or Many shops managed by directors.

    If I refer to the AW sample DB, where and how I will refer those then ?

    Does shops Owner will be identify as a single Person ?
    Does Shops Owners shops will be define in Store table ?

    What would be the best implementation 

    Wednesday, February 19, 2014 11:13 AM
  • >So "MY COMPANY" can have One or Many ShopsOwner and ShopsOnwers can have One or Many shops managed by directors.

    AdventureWorks Cycles fictional company does not have this structure.

    You can implement the hierarchy by Shop FK to ShopOwner PK and ShopOwner FK to MyCompany PK.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by wakefun Wednesday, February 19, 2014 5:28 PM
    Wednesday, February 19, 2014 1:45 PM
  • Wednesday, February 19, 2014 2:06 PM
    Answerer