Canonical Calendar creating Synthetic Keys and Cyclical Structures

[Domino3145]

Hi all,

I'm developing a database for my company on policy information. So far I have 2 tables, Policy Information and Payment History. Each Policy can have multiple payments.

I'm trying to create a canonical calendar to normalise the 3 date fields I have in my database; 'Earliest Policy Inception Date', 'Payment Received Date' and 'Payment Process Date'. This is so that I can have one date field control multiple visualisations and graphs in one dashboard.

Each Policy will only have 1 'Earliest Policy Inception Date' but multiple of the other 2 dates, meaning I cant join to the LinkTable by [Policy Number] alone, but when I join 'Earliest Policy Inception Date' to the LinkTable and fields in Payment History by UniquePaymentId, I get synthetic keys because UniquePaymentId and [Policy Number] exist in both the LinkTable and PaymentHistory.

Am I missing something? I need a scalable solution (I will have many more date fields from different tables regarding claims, complaints etc. belonging to each Policy) where I wont lose any granularity in my data either. How do I go about this?

[-SW-]

After adding the UniquePaymentID, and Policy Number from PaymentHistory to the link table, you can drop Policy Number from payment History

[Domino3145]

Thanks for the reply! When I do this I lose granularity between PolicyInformation and PaymentHistory because for rows in the link table that use [Earliest Policy Inception Date] as the Canonical Date field, they have no value in the UniquePaymentId field. So if I have a visualisation using the DateType 'Policy Start Date' and i make any selections in the PaymentHistory table, the visualisation will appear null. 

Here's an example of what I mean, this is my linktable for a single policy number

Have I built my link table as a concatenate table instead and thats the issue? Im still a little unsure of the difference to be honest

[-SW-]

If your Policy information doesnt change, and you already added the date into the link table, would it make more sense to join that into your paymenthistory table?

[-SW-]

or join and then add the dates into the link table as needed.

[Domino3145]

If I understand right, you're saying I could just join my PaymentHistory and PolicyInformation tables as its a 1 to many connection and it wouldn't create any new rows, then join to the link table using a composite key of [Policy Number] + [UniquePaymentId]? 

Would this be a scalable solution? Im currently working with 1 fact table and 1 dimension table, but eventually I'm going to have 3/4 fact tables (PaymentHistory, Claims, Complaints, PolicyInstance) with their own sets of date fields and multiple dimension tables (like scheme codes). Wouldn't your solution mean I'd have to join all of these tables in to one massive one with tonnes of duplicates?

[-SW-]

ahh, did not know about the other future tables.

In that case I would move date type out of your calendar. Your link table should only have the key fields from all your other tables, and your date type would be the inbetween from your link to calendar

[Domino3145]

Thanks again for the reply, really appreciate the help! Apologies I'm having a little trouble interpreting what you mean. I should take [Date Type] out of my LinkTable, then my LinkTable should contain only my key fields from other tables so that would be [Policy Number], UniquePaymentId, UniqueClaimId, UniqueComplaintId. I understand how that database structure would look, but what do you mean by 'Date Type' would be the in between from my link to calendar? 

What would I join the bridge to the linktable by?

[-SW-]

I dont want to lead you down the wrong way, can you provide samples of raw data (with sensitive info removed) of your datamodel above, and also your claim table so that I can show?

[Domino3145]

Sure, thanks again! I've assembled all the data I'll need now for this project.

This is currently how my data model is structured. I concatenated all of the key fields into the LinkTable, leaving the [Policy Number] field on each row but removing it from each fact table, allowing them to join to the LinkTable by their key fields (UniquePaymentId, PolicyClaimId etc.)

This is currently what my LinkTable looks like once I'd concatenated my other tables into it and created this DateType field. This is only looking at a single Policy.

The top row is the [Earliest Policy Start Date]. This linktable is currently joined to the rest of the master calendar by %CanonicalDate. The issue with this current model im having is if i make a selection in any field in PaymentHistory but a visualisation is showing say Policies with Claims over a certain date range, the visualisation would appear blank because its using a different DateType field. My ideal situation would be the visualisation still show policies with claims over a certain date range, just only those which have made a payment amount of £50 for example (by filtering with the payment amount field in PaymentHistory).