Solved

How to best relate columns between tables that participate in multi-column primary keys

Posted on 2015-01-14
4
109 Views
Last Modified: 2015-05-24
Hi All

For some reason I'm drawing a blank on this one.

Two tables: Account (a) and orders (o).  
Account has as a primary key id and dt_start, where id identifies the person, and there could be multiple rows for the same id with a different dt_start when a row changes.   For example, if an account was changed twice, there will be three rows:  The original one, and two others with the updated date as dt, and the updated rows.
Orders has as a primary key id and dt, and similar to the above if an order changes there will be multiple rows with the same order but different dt.  Column a_id is a FK for a.id.

Big Hairy Question How can you define a relationship between tables where the column being related is part of a multi-column primary key?  OR should I redesign the tables so that only column id is the primary key, and history is kept another way?

The below 2008R2 code illustrates my question:
-- Accounts:  People.  Can be multiple rows with a different dt if it changes. 
IF OBJECT_ID('tempdb..#a') IS NOT NULL
	DROP TABLE #a
GO

CREATE TABLE #a (id int NOT NULL, dt_start date NOT NULL, dt_end date, name varchar(25)) 

-- Add a FK on id, dt
ALTER TABLE #a
ADD CONSTRAINT a_fk_id_dt PRIMARY KEY (id, dt_start) 

INSERT INTO #a (id, dt_start, dt_end, name) 
VALUES 
	(1, '2014-01-01', '2014-01-02', 'Bob'), (1, '2014-01-03', '2014-01-10', 'Bobby'), (1, '2014-01-11', NULL, 'Boba Fett'),
	(2, '2014-01-15', '2014-01-17', 'Joe'), (2, '2014-01-18', NULL, 'Joey'),
	(3, '2014-01-15', '2014-01-20', 'Chuck'), (3, '2014-01-21', '2014-02-15', 'Chuck E. Cheese'), (3, '2014-02-16', NULL, 'Charlie Hebdo'),
	(4, '2014-01-15', NULL, 'Frank')
	
	
-- Orders:  One to many for a given #a.id
IF OBJECT_ID('tempdb..#o') IS NOT NULL
	DROP TABLE #o
GO

CREATE TABLE #o (id int NOT NULL, dt date NOT NULL, a_id int NOT NULL, amount money) 

-- Add a FK on id, dt
ALTER TABLE #o
ADD CONSTRAINT o_fk_id_dt PRIMARY KEY (id, dt) 

INSERT INTO #o (id, dt, a_id, amount) 
VALUES 
	(1,'2014-01-05', 1, 100), 
	(2,'2014-01-06', 1, 1000), 
	(3,'2014-01-15', 1, 1.98), 
	(4,'2014-01-15', 2, 200), 
	(5,'2014-02-15', 2, 300), 
	(6,'2014-01-20', 4, 301)



-- MY BIG HAIRY QUESTION:  How can I create a FK between #a.id and #o.a_id, that ignors the dt columns?  
SELECT a.id as account_id, a.name, o.id as order_id, o.dt as order_date, o.amount
FROM #o o
   JOIN #a a ON a.id = o.a_id AND o.dt BETWEEN a.dt_start AND ISNULL(a.dt_end, '2050-01-01')
ORDER BY o.dt, a.id

Open in new window


Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 400 total points
Comment Utility
So you only need to see the current row from each table?  You'd need to filter that using ROW_NUMBER() or something similar.

You can't create an actual FK on that, because you don't have a unique index on just id.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
Comment Utility
Jim, I think you're having this issue because your database isn't normalized. Your table Account as a PK (id, dt) that I think the ID should be a FK relating to a master table. Same thing to Order table.
0
 
LVL 65

Author Comment

by:Jim Horn
Comment Utility
>So you only need to see the current row from each table?  
Scott - I'm handling 'current rows' and 'history' in two separate views for each source table right now, but that doesn't allow me to create table relationships.

>Jim, I think you're having this issue because your database isn't normalized.
Correct.  I've been asked to build an SSIS package where the source is Salesforce.com, all Type 1 SCD's, and populate it in a SQL Server database that does not delete outdated rows, making it a Type 2's, so that's how I've stored the data.  

I've been wrestling with the needs of the 'give me the current row' users vs. 'need the whole history' users, hence the separate views.  I don't think I'm going to be able to get away with it for long though, as I currently can't create foreign key relationships with the date columns/history in the picture.

So .. your comments confirm that I'll likely have to build another database with just the current rows, establish the relationships, and add to my current ETL package that build.   Thanks for confirming.

Unless I see any other solution in the next day or so I'll close the question and split points.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
Comment Utility
your comments confirm that I'll likely have to build another database with just the current rows
That's an option but instead of a database could be a schema or even a view only with SELECT DISTINCT ID.... so you can join the view with tables.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now