5 Replies Latest reply: Feb 17, 2016 5:06 PM by Muliady Hartono RSS

    Creating a data model without syntehtic keys

    ASma Erum

      I have been working on this for a month and I have taken the data modeling course and I am still a little confused as to how I would make the data model work without adding in synthetic keys.

       

      data model1.PNG

       

      Sales and Purchase table has been concatenated into Sales and Purchases. There can be more than 1 particular record on the same date with more than one sales/purchase order. When I map the InventoryKey and the Warehouse to the Dailyinventorystatus table I get a synthetic key. I created an nventoryStatId which is the autoHasNumber using the warehouse, Transdate, and InventoryKey, but i am unsure how to link that to the SalesAndPurchase table. I know I should look at creating a fact table, but at the same time I do not want to create duplicate records. I.e if I do a left join on the Sales and Purchase Table, because there are multiple purchases/sales for the same item on the same day it will create a duplicate on the dailyinventorystatus table. Help would be immensely appreciated.

       

      Also incase anyone is wondering the masterid maps to the InventoryKey.
      How do I create a data model. This has had me scratching my head for ages.