Solved

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

Posted on 2015-01-14
4
110 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
ID: 40550177
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 40550806
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
ID: 40552307
>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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 40552969
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

930 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

14 Experts available now in Live!

Get 1:1 Help Now