Bridge table FK query

Hi all, I'm having some difficulty in understanding in how the bridge table has been explained in the book "The Data Warehouse Toolkit, 3rd Edition".

This is a simple case of an automobile policy on which multiple drivers are permitted to drive. In the following diagram, I understand that the "Insured Driver Dimension" table has a 1:M relationship with the "Policy-Insured Driver Bridge" table. What I don't understand is the relationship between "Policy-Insured Driver Bridge" & the "Premium Snapshot Fact" table. The "Policy-Insured Driver Bridge" doesn't have a primary key so how can the two be related?


Thank you in advance.

Hi - the "Policy-Insured Driver Bridge" does have a primary key - it consists of two columns: "Policy Key" and "Insured Driver Key".

Hope this helps?

Regards

Thanks Nick.

But there are two problems:
a) In that case the fact table should have both the FK columns right?
b) The premium snapshot fact table should only relate to a policy and not any of the permitted drivers, which will not be so if the above point is true

I hope I am able to clarify...

I'm not sure why you think that both FKs are needed in the Fact table:

SELECT *
FROM FACT, BRIDGE, DIM
WHERE FACT.POLICY_KEY = BRIDGE.POLICY_KEY
AND BRIDGE.INSURED_DRIVER_KEY = DIM.INSURED_DRIVER_KEY

The resultset from this query will obviously contain more records that exist in the fact table - but that's the whole point as it is modelling a M:M relationship. The weighting factor in the bridge table is there to allow you to apportion a percentage of the fact measure values to the Dim records so that the total value is not being double counted.

For example, a policy has 2 insured drivers. You have one record in the fact, 2 records in the bridge and 2 records in the dim. If you wanted to apportion 75% of the premium to Driver1 and 25% to Driver2 then in the bridge table the record linking the fact to Driver1 might have a weighting of 0.75 and and in the other bridge record a value of 0.25. You then multiple the measures by the weighting when writing the query.

Does this explain it better?

Hi again Nick,

The query you've suggested will work even when there is no relationship between the fact table and the bridge table. I may be wrong but I'm beginning to think that the relationship between the fact and the bridge table is purely logical and cannot be implemented physically in a database.

In a relational database, if a table has a composite PK comprising of two columns (POLICY KEY + INSURED DRIVER KEY in case of the bridge table), then any child table (i.e. the fact table) with FK references to that table must have both the columns to fulfill the referential integrity.

Let me illustrate (you'll see that the attempt to create a FK relationship between the fact table and the bridge table fails with only one column, but succeeds with both the columns):

Code:

>CREATE TABLE policy_dim (
  2    policy_key NUMBER,
  3    --and other columns
  4    policy_description VARCHAR2(30));

Table created.

>ALTER TABLE policy_dim ADD CONSTRAINT pk_pold PRIMARY KEY(policy_key);

Table altered.

>CREATE TABLE insured_driver_dim (
  2    insured_driver_key NUMBER,
  3    insured_driver_name VARCHAR2(30));

Table created.

>ALTER TABLE insured_driver_dim ADD CONSTRAINT pk_insdd PRIMARY KEY(insured_driver_key);

Table altered.

>CREATE TABLE policy_insured_driver_bridge (
  2    policy_key NUMBER,
  3    insured_driver_key NUMBER,
  4    weightage NUMBER);

Table created.

>ALTER TABLE policy_insured_driver_bridge ADD CONSTRAINT pk_polidb PRIMARY KEY(policy_key, insured_driver_key);

Table altered.

>ALTER TABLE policy_insured_driver_bridge ADD CONSTRAINT fk_polidb_inspd FOREIGN KEY(policy_key) REFERENCES policy_dim(policy_key);

Table altered.

>ALTER TABLE policy_insured_driver_bridge ADD CONSTRAINT fk_polidb_insdd FOREIGN KEY(insured_driver_key) REFERENCES insured_driver_dim(insured_driver_key);

Table altered.

>CREATE TABLE premium_snapshot_fact (
  2    --date keys
  3    policy_key NUMBER,
  4    --No insured_driver_key column
  5    written_premium NUMBER);

Table created.

>--Now try to add a foreign key to bridge table with only policy_key
>ALTER TABLE premium_snapshot_fact ADD CONSTRAINT fk_presf_polidb FOREIGN KEY(policy_key) REFERENCES policy_insured_driver_bridge(policy_key);
ALTER TABLE premium_snapshot_fact ADD CONSTRAINT fk_presf_polidb FOREIGN KEY(policy_key) REFERENCES policy_insured_driver_bridge(policy_key)
                                                                                                                                 *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

>--Now let's add the column insured_driver_key to the premium_snapshot_fact table
>ALTER TABLE premium_snapshot_fact ADD (
  2    insured_driver_key NUMBER);

Table altered.

>--And try to add a foreign key to bridge table with both policy_key and insured_driver_key
>ALTER TABLE premium_snapshot_fact ADD CONSTRAINT fk_presf_polidb FOREIGN KEY(policy_key, insured_driver_key) REFERENCES policy_insured_driver_bridge(policy_key, insured_driver_key);

Table altered.


OK - I understand the point you are making now but I'm guessing you come from an OLTP DB background (as most of us do!) and are trying to apply what you know about OLTP DB design to OLAP DB (star schema) design.

In an OLAP DB you would never create FKs as it kills the data load performance. If you do create them then you'd have to drop them before you load data into your DB and then re-create them when the load has finished. In any case, there is no point in having them as all your referential integrity should be built into your ETL processes - prior to loading data into the facts/dimensions/bridge tables.
The only time I implement FKs is when I'm using a modelling tool (and want to forward/reverse engineer between the model, with its links, and the DB) - but then I ensure they are disabled so they don't actually impact anything.

PKs on Dimensions are fine but don't normally do anything (as the PK'd column will be a Surrogate Key with no business meaning and so, probably, won't stop you loading the same source record twice as the PK is not the business key/source system PK).

PKs on Fact can be useful if you have to update the Fact table but other than that are not normally required

Regards,

This might surprise you...

Interesting but, I would argue, mostly irrelevant - because he is writing about OLTP-designed DBs and not OLAP designs (I normally work on the assumption that if someone does not explicitly say they are discussing OLAP then they are talking about OLTP).

An OLAP is all about performance: mainly query performance but also load performance. You often have two sets of indices - one for load performance and one for query performance and you enable/disable each set depending on what you are doing to your DB (overnight daily load or allowing users to run queries during the working day).

Anything you add to your DB should only be there to improve performance and you should remove anything that hinders performance. Given that implementing FK referential integrity is going to kill your data load performance and is not going to help your query performance (as you will create indices specifically to speed up your queries) - why would you want to implement FKs?

As an aside, even when you are dealing with an OLTP DB you don't necessarily have FKs. I've spent much of my working life using a Enterprise CRM application where the application manages the data integrity and therefore it has no FKs defined in the DB

Regards,

I have benchmarked that adding FK with NOVALIDATE and RELY predicates does not affect load time.

It merely gives more information to the (Oracle) database to optimize queries, especially with regards to using materialized views (as the article demonstrated), which are more applicable to OLAP/DWH environments.

Anyway that's a slight but interesting diversion from the original topic.

And thank you for your inputs.