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
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)
(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
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)
(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
Thanks in advance.