Regarding my Scenario

[#1]

Dear Friends,

I appreciate your time! I have my scenario as follows, kindly share the best way to design my data model.

My table with field details below:

Sales Table : CustomerNumber,Customer,Customer Kind,DivisionNumber,Territory,Sales,Date,SalesPerson

Customer Table : CustomerNumber,Customer,Customer Kind,DivisionNumber,Territory,Business,Phone

Division Lookup Table : Division Number,Division Name

Territory Lookup Table : Territory,Territory Name.

If the field  values of the tables Customer,Customer Type,Division,Region are same in both Sales and Customer table. What is the best way to build my data model as I need to calculate the sales based on SalesPerson. Kindly share your ideas.

I have removed CustomerType field in Sales Table, DivisionNumber and Territory Fields in Sales Table to avoid synthetic key.

Is that ok?

Thank you,

Jeevi

[aarkay29]

I believe the best approach here is to make a composite key by concatenating Customer,Customer Kind,DivisionNumber,Territory

Sales:

Load

      Customer&Customer Kind&DivisionNumber&Territory as %Key,

     Customer,Customer Kind,DivisionNumber,Territory,Sales,Date,SalesPerson

From Sales;

Customer

Load

     Customer&Customer Kind&DivisionNumber&Territory as %Key,

     Business,Phone

From Customer;

Division:

Load

     Division Number,Division Name

From Division;

Territory:

Load

     Territory,Territory Name

From territory;

[#3]

Hello Aar Kay,

I appreciate your time. I actually have CustomerNumber field along with Customer,Customer Kind,DivisionNumber,Territory fields in both sales and customer table. So I am using that to connect between these tables.

IS that ok,kindly let me know!

Thank you!

Jeevi

[#4]

Dear Friends,

Kindly let me know your suggestions!

Thank you!

[aarkay29]

If Cutomer Number is a unique number with each Number associated with unique/only one Customer field then you can just link by Customer Number  else use the composite key by concatenating the fields!!!

Hope it helps

Thanks,

Aar.

[Kushal_Chawda]

try this

Use autonumber on key as it is composite key

Sales:

Load

      Autonumber(CustomerNumber & Customer Kind & DivisionNumber &Territory) as Key,

      Sales,Date,SalesPerson

From Sales;

Customer:

Load

     Autonumber(CustomerNumber & Customer Kind & DivisionNumber &Territory) as Key,

     Customer,CustomerNumber,Customer Kind,DivisionNumber,Territory,Business,Phone

From Customer;

left join(Customer)

Load

     Division Number,Division Name

From Division;

left join(Customer)

Load

     Territory,Territory Name

From territory;