I would like to simplify my data with a view table, MainView but am having a hard time figuring it out.
I have a Fact table that is specific to clients, language, and status. The ID in the Fact table comes from a FactLink table that just has an FactLinkID column. The Status table has an Order column that needs to be shown in the aggregate view instead of the StatusID. My Main table references the Fact table in multiple columns.
The end goal will be to be able to query the view table by the compound index of LanguageID, StatusOrder, ClientID more simply than I was before, grabbing the largest specified StatusOrder and the specified ClientID or ClientID 1. So, that is what I was hoping to simplify with the view table.
So,
Main
ID | DescriptionID | DisclaimerID | Other
----+---------------+--------------+-------------
50 | 1 | 2 | Blah
55 | 4 | 3 | Blah Blah
Fact
FactID | LanguageID | StatusID | ClientID | Description
-------+------------+----------+----------+------------
1 | 1 | 1 | 1 | Some text
1 | 2 | 1 | 1 | Otro texto
1 | 1 | 3 | 2 | Modified text
2 | 1 | 1 | 1 | Disclaimer1
3 | 1 | 1 | 1 | Disclaimer2
4 | 1 | 1 | 1 | Some text 2
FactLink
ID
--
1
2
3
4
Status
ID | Order
---+------
1 | 10
2 | 100
3 | 20
MainView
MainID | StatusOrder | LanguageID | ClientID | Description | Disclaimer | Other
-------+-------------+------------+----------+---------------+-------------+------
50 | 10 | 1 | 1 | Some text | Disclaimer1 | Blah
50 | 10 | 2 | 1 | Otro texto | NULL | Blah
50 | 20 | 1 | 2 | Modified text | NULL | Blah
55 | 10 | 1 | 1 | Some text 2 | Disclaimer2 | Blah Blah
Here's how I implemented it with just a single column that references the Fact table:
DROP VIEW IF EXISTS dbo.KeywordView
GO
CREATE VIEW dbo.KeywordView
WITH SCHEMABINDING
AS
SELECT t.KeywordID, f.ClientID, f.Description Keyword, f.LanguageID, s.[Order] StatusOrder
FROM dbo.Keyword t
JOIN dbo.Fact f
ON f.FactLinkID = t.KeywordID
JOIN dbo.Status s
ON f.StatusID = s.StatusID
GO
CREATE UNIQUE CLUSTERED INDEX KeywordIndex
ON dbo.KeywordView (KeywordID, ClientID, LanguageID, StatusOrder)
My previous query queried for everything except for that StatusOrder. But adding in the StatusOrder seems to complicate things. Here's my previous query without the StatusOrder. When I created a view on a table with just a single Fact linked column it greatly simplified things, but extending that to two or more columns has proven difficult!
SELECT
Main.ID,
COALESCE(fDescription.Description, dfDescription.Description) Description,
COALESCE(fDisclaimer.Description, dfDisclaimer.Description) Disclaimer,
Main.Other
FROM Main
LEFT OUTER JOIN Fact fDescription
ON fDescription.FactLinkID = Main.DescriptionID
AND fDescription.ClientID = @clientID
AND fDescription.LanguageID = @langID
AND fDescription.StatusID = @statusID -- This actually needs to get the largest `StatusOrder`, not the `StatusID`.
LEFT OUTER JOIN Fact dfDescription
ON dfDescription.FactLinkID = Main.DescriptionID
AND dfDescription.ClientID = 1
AND dfDescription.LanguageID = @langID
AND dfDescription.StatusID = @statusID
... -- Same for Disclaimer
WHERE Main.ID = 50
Not sure if this the most performant or elegant way to solve this problem. But I finally thought of a way to do it. The problem with the solution below is that it can no longer be indexed. So, now to figure out how to do that without having to wrap it in a derived table.
SELECT
x.ID,
x.StatusOrder,
x.LanguageID,
x.ClientID,
x.Other,
MAX(x.Description),
MAX(x.Disclaimer)
FROM (
SELECT
Main.ID,
s.StatusOrder,
f.LanguageID,
f.ClientID,
f.Description,
NULL Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
Main.ID,
s.StatusOrder,
f.LanguageID,
f.ClientID,
NULL Description,
f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID
) x
GROUP BY x.ID, x.StatusOrder, x.LanguageID, x.ClientID, x.Other