Solved

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

Posted on 2015-01-14
4
111 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:
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 47

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 47

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 34
Slow Connectivity over ODBC 8 32
Sql server function help 15 28
Query Help - MSSQL - Averages 5 25
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

808 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