One to many Relationships

[sudhakar_budde]

Hi,

I have table Client (where Client_type in (Clients, Agents and Contacts)). Primary Key is CL_ID.

I have another table WorkOrder with Cleint_ID, Agency_ID and Contact_ID. All these IDs join on Clients.CL_ID.

So I have derived Clients (CL_ID as %_CLIENT), Agency (%_AGENCY) and Contacts (%_CONTACT) from Client table and Joined with their respective Cleint_ID(%_CLIENT), Agency_ID (%_AGENCY) and Contact_ID (%_CONTACT) in the WorkOrder table.

Please see attached table view.

My question is : is this approach correct? What is the best way to reduce these tables instead of deriving more? How can I resolve this one-to-many relationship? Please help me.

Thanks in advance

[#2]

Instead making 3 client  dimension tables , create one Client table with concatenate other client types like below.

Clients:

Load

     *,

     'Clients' as %Client_Type // this field differentiate type of client

From Clients.qvd (qvd) ;

concatenate (Clients)

Load

     *,

     'Agents' as %Client_Type // this field differentiate type of client

From Agents.qvd (qvd) ;

concatenate (Clients)

Load

     *,

     'Contacts' as %Client_Type // this field differentiate type of client

From Contacts.qvd (qvd) ;

[sudhakar_budde]

Hi Dathu,

Thanks for your reply.

But I have 3 keys  Cleint_ID(%_CLIENT), Agency_ID (%_AGENCY) and Contact_ID (%_CONTACT) in the WorkOrder table. So, how can I join? Please see my attachment.

[Peter_Cammaert · Accepted solution]

You did a nice job of creating a star schema out of a compact database representation (all contacts of whatever type in a single table) And if it works, then it's correct.

A subsequent question you can ask yourself: is this an optimal solution? Is performance hampered by the extra hops and should you merge the fields you really need into the workorder table and throw away the others (throwing away what you don't really need is always a good thing)? Is there a massive amount of data present in the document, or is size rather limited and are we optimizing for fun while getting no real improvements...

A good read by HIC: A Myth about the Number of Hops

Another one by Miguel: The Importance Of Being Distinct

And another more philosophical post by HIC about what to do when: Data Modelling: Clarity vs. Speed

Peter

[#5]

You can rename the field names make into one common field between Fact & Dimension. I believe you have split your Fact table as well. Can you please post sample data with small no if rows ?