Solved

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

Posted on 2015-01-14
4
113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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 49

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 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard 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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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