settingsLogin | Registersettings
Scalefree
Show Menu

A combo of 1:1 and 1:n relation

0 votes

Hi guys,

I've started modeling what should be my first DV driven data warehouse. After reading both books, super charge.. and dv 2.0.., I took a swing at our use case.

 

The source system involved in this use case is a service management system used for registering customer cases (Ticket) including related information like, Contract, CI, Service and Customer.

In the source system I've identified the following relations:

  • Ticket > CI (1:n) NOTE: 1 CI is always marked as primary CI
  • Ticket > Service (1:1)
  • Ticket > Customer (1:1)
  • Ticket > Contract (1:1)

I started by linking all the hubs (Ticket, CI, Service, Customer, Contract) together with one single link (Service Request). But this will not do the trick since a Ticket can have multiple CI's (1:n) and I'll end up by storing duplicate information in case a Tickets belogns to multiple CI's.

So I'm struggling to find the best DV approach which will not compromise the DV standard. So far I came up with two alternatives but I'm unable to identify the consequences...

1. Seperate links between hubs

Model seperate links between the hubs, but this doesn't fit the grain of the source system. Becasue when a new ticket is stored in the system the relations listed above are stored.

2. Seperate link between Ticket and CI

Model a Service Request link to link Ticket, Service, Customer and Contract, and a seperate link between Ticket and CI

 

Another question is, how can I solve this problem. What kind of question should I ask myself or the business? What information can be used from the source model to solve this?

 

Thank you!

 

Regards,

Bas

asked Oct 22, 2015 in Modelling by bas_vdl (140 points)

2 Answers

0 votes
Dear Bas,

are all these relations in one source table? e.g. "ticket" ?

Can you share a E/R model?

Thank you kindly,

Mike
answered Oct 22, 2015 by molschimke (1,890 points)
Hi Mike,

Thank you for taking a moment to look into my case. There is no ERD available, the source database only consist of PK's all other constraints like FK's are not defined...

As we go we try to puzzle this together.

The following 'FKs' are present in the Ticket table:
ticket.account_id < 1:1 > (Customer)
ticket.serv_cont_id < 1:1 > (Contract)
ticket.it_service < 1:1 > (Service)

For CI's, a 1:n relation is applied:
ticket.id < 1:n > (TicketCI) < n:1 > (CI)

I hope this is the required information you were looking for.

Again, thank you.

Regards,

Bas
edit: created account and placed comment again.
0 votes
I would model this using your first sugestion of seperate links. 5 Hubs of Ticket, Cusotmer, Service, Contract and CI and 4 links connecting these together. You could then use effecitvity satellites to show which are the active records and track the history, as I assume that all these fields may be updated on each ticket.
answered Nov 21, 2015 by Liam Whales
Hi Liam, I went with separate links. This will give us the most flexibility.
Excellent, hope the model is working well for you
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree
    DataVault.guru is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license.

    DataVault.guru | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke
    ...