I want to import some data from a 3rd Party database.
It has a Child Table for "Parties" associated with a Matter table. Even if there can only ever be one party, such as Client, the Client Name ID is still stored in the Party child table. Other Party Types, such as Debtor, allow a maximum of two entries per Matter, and there are other things, such as Interested Parties, Licensees, Owners etc, which allow unlimited values.
(There is a separate Party Type Definition Table which has a "Max allowed" column).
There is an Item Number column in the Party table, which lets me decide which is the First Debtor. I am not certain that the Item column is guaranteed to be consecutive numbers 1, 2, 3, ... thus I am assuming that the First Debtor has the lowest Item No, rather than "1".
My Import routine requires a View to provide all the columns that it needs to import from i.e. matching the Target Table (and from there the import routine decides which rows have a modified-value more recent than "last time", and data validation issues, and so on).
I'm bothered that my approach is very inefficient! Here's what I have got:
Create tables for Matter, Name and Party:
CREATE TABLE dbo.TEMP_Matter
(
M_ID int NOT NULL,
M_Title varchar(30) NULL,
PRIMARY KEY
(
M_ID
)
)
GO
CREATE TABLE dbo.TEMP_Name
(
N_ID int NOT NULL,
N_Name varchar(30) NULL,
PRIMARY KEY
(
N_ID
)
)
GO
CREATE TABLE dbo.TEMP_Party
(
P_M_ID int NOT NULL,
P_N_ID int NOT NULL,
P_Type char(1) NOT NULL,
P_Item int NOT NULL,
PRIMARY KEY
(
P_M_ID,
P_N_ID,
P_Type,
P_Item
)
)
GO
Populate with some data
INSERT INTO dbo.TEMP_Matter
VALUES(101, 'MatterA'),(102, 'MatterB')
INSERT INTO dbo.TEMP_Name
VALUES(201, 'ClientA'),(202, 'ClientB'),(203, 'ClientC'),(204, 'ClientD')
INSERT INTO dbo.TEMP_Party
VALUES
(101, 201, 'C', 1) -- Client : First Matter, ClientA
, (102, 202, 'C', 1) -- Client : Second Matter, ClientB
, (101, 201, 'D', 1) -- Debtor : First Matter, ClientA
, (102, 202, 'D', 1) -- Debtor1 : Second Matter, ClientB
, (102, 203, 'D', 2) -- Debtor2 : Second Matter, ClientC
GO
Create a VIEW to display the First Party, for a given Matter
CREATE VIEW dbo.TEMP_Party_First_View
AS
SELECT P_M_ID,
P_N_ID,
P_Type,
P_Item
FROM dbo.TEMP_Party AS P1
WHERE P_Item =
(
SELECT MIN(P_Item)
FROM dbo.TEMP_Party AS P2
WHERE P2.P_M_ID = P1.P_M_ID
AND P2.P_Type = P1.P_Type
)
GO
I used MIN as I assumed it would be more efficient than ROW_NUMBER(), but clearly in order to be able to get Second/Subsequent parties I would plan to use ROW_NUMBER()
Then create a view to get the Matter and the Parties that can be "flattened" (in my example assume that there is only one Client and a max of Two Debtors
CREATE VIEW dbo.TEMP_Matter_Import_View
AS
SELECT M_ID,
M_Title,
[M_N_ID_Client] = Client.P_N_ID,
[M_N_ID_Debtor1] = Debtor.P_N_ID
FROM dbo.TEMP_Matter AS M
LEFT OUTER JOIN dbo.TEMP_Party_First_View AS Client
ON Client.P_M_ID = M.M_ID
AND Client.P_Type = 'C' -- "Client"
LEFT OUTER JOIN dbo.TEMP_Party_First_View AS Debtor
ON Debtor.P_M_ID = M.M_ID
AND Debtor.P_Type = 'D' -- "Debtor"
-- Going to need to use something different to get the Second Debtor!
GO
Display the data
SELECT 'Matter', *
FROM dbo.TEMP_Matter
SELECT 'Name', *
FROM dbo.TEMP_Name
SELECT 'Party', *
FROM dbo.TEMP_Party
JOIN dbo.TEMP_Matter
ON M_ID = P_M_ID
JOIN dbo.TEMP_Name
ON N_ID = P_N_ID
SELECT 'Matter_Import', *
FROM dbo.TEMP_Matter_Import_View
I'm particularly bothered that the TEMP_Party_First_View has to be LEFT OUTER JOIN'd multiple times, once for each Party Type. There are about half-a-dozen Party Types that only allow a max of one value, and a few more than allow a max of Two - both of which I would like to flatten on Import to speed the reporting side (not having to JOIN to Party Child Table to find things for which only one value can exist).
EDIT: The Item numbers are definitely not suitable for use as consecutive 1,2,3 sequence as (I had forgotten) there is an ExpiryDate column in the Party table, and I only want to use rows which have not expired.