I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different Columns. The important part is that with this structure I can define different datatypes for each Column.
Those could be divided in two groups: Natives (defined in the Native_Types table) and SheetType from the already created Sheets.
Code to get unpivoted data:
CREATE VIEW RowsData
AS
SELECT r.ID, s.ID SheetID, col.ID ColID,
col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
FROM Sheets s
INNER JOIN [Columns] col ON s.ID = col.SheetID
INNER JOIN [Rows] r ON s.ID = r.SheetID
INNER JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID
LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO
Sample data:
ID SheetID ColID Name NativeValue
----------- ----------- ----------- ------------------ ------------
1 1 1 Person Name Aron
1 1 2 Person Country Uruguay
2 1 1 Person Name Sam
3 2 4 Country Name Uruguay
4 2 4 Country Name USA
5 2 4 Country Name Russia
6 3 5 Pet Name Mickey
6 3 6 Pet Age 83
6 3 7 Pet Person Owner Aron
7 3 5 Pet Name Pluto
7 3 6 Pet Age 8
7 3 7 Pet Person Owner Aron
8 3 5 Pet Name Zabivaka
8 3 6 Pet Age 4
8 3 7 Pet Person Owner Sam
From here is all ok and any suggestion if there is a better way or any improvements are welcome!
Weird stuff begins
Then I've implemented some kind of inheritance over Columns and its Cells.
For instance defining new Column for Sheet 3 (Pets) called "Pet Country" (setting it SheetTypeID -> 2, Countries). Now Pets have a Column "Pet Country" but instead of define a Cell value for each one if I define this Column as inherited (and particular structure to support in which way) I could inherit the Country value from "Pet Owner". Remember that "Pet Owner" is of SheetTypeID 1 (Persons) and this Sheet have a Column of SheetTypeID 2 (Countries).
By simplifying I mean that if I have a Pet that has a Column "Pet Owner" and "Pet Country," I don't want to specify manually the "Pet Country" value, I want to inherit it from "Pet Owner" related with each Pet.
To accomplish that I have added two auxiliary fields to Columns table:
ColumnSameSheetInheritFromID-> This defines from which Column of the same Sheet thisColumnmust inherit (must be Sheet type Column).ColumnRelatedToFromColumnTypeID-> This defines from which Column of the Sheet type of the aboveColumnmust thisCampoget the real inheritedCellvalue then.
Adjusted diagram:
Helper self referenced function to get inherited Cells from Columns (here is where I presume should be a better way to do things)
CREATE FUNCTION [dbo].[GetInheritedCells](@rowID INT, @colID INT)
RETURNS @result TABLE (ID INT)
AS
BEGIN
DECLARE @ColumnSameSheetInheritFromID INT = NULL;
DECLARE @ColumnRelatedToFromColumnTypeID INT = NULL;
--GETTING COLUMN FROM AND COLUMN VALUE IDs
SELECT @ColumnSameSheetInheritFromID = col.ColumnSameSheetInheritFromID,
@ColumnRelatedToFromColumnTypeID = col.ColumnRelatedToFromColumnTypeID
FROM dbo.[Columns] col
WHERE col.ID = @colID;
--IF COLUMN IS NOT INHERITED FUNCTION ENDS
IF @ColumnSameSheetInheritFromID IS NULL OR @ColumnRelatedToFromColumnTypeID IS NULL
BEGIN
RETURN;
END
DECLARE @cellFromID INT = NULL;
DECLARE @cellInhValueID INT = NULL;
DECLARE @colFromAUX_ID INT = NULL;
DECLARE @cellHaveValue INT = NULL;
--GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW
SELECT @cellFromID = cls.ID,
@colFromAUX_ID = col.ColumnSameSheetInheritFromID,
@cellHaveValue = IIF(cls.RowValueID IS NULL, 0, 1)
FROM dbo.[Rows] r
LEFT OUTER JOIN dbo.[Columns] col
ON r.SheetID = col.SheetID
LEFT OUTER JOIN dbo.[Cells] cls
ON r.ID = cls.RowID AND col.ID = cls.ColumnID
WHERE r.ID = @rowID AND col.ID = @ColumnSameSheetInheritFromID
IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
SET @cellFromID = (SELECT TOP 1 ID FROM dbo.GetInheritedCells(@rowID, @ColumnSameSheetInheritFromID));
ELSE IF @cellFromID IS NULL AND @ColumnSameSheetInheritFromID IS NULL
SET @cellFromID = -1;
IF @cellFromID > 0
BEGIN
SELECT @rowID = cls.RowValueID
FROM dbo.[Cells] cls INNER JOIN dbo.[Columns] col ON cls.ColumnID = col.ID
WHERE cls.ID = @cellFromID;
IF @rowID IS NOT NULL
BEGIN
--GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW
SELECT @cellInhValueID = cls.ID,
@colFromAUX_ID = col.ColumnSameSheetInheritFromID,
@cellHaveValue = (IIF(cls.RowValueID IS NULL, IIF(cls.NativeValue IS NULL, 0, 1),1))
FROM dbo.[Rows] r
LEFT OUTER JOIN dbo.[Columns] col
ON r.SheetID = col.SheetID
LEFT OUTER JOIN dbo.[Cells] cls
ON r.ID = cls.RowID AND col.ID = cls.ColumnID
WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID
IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
BEGIN
INSERT INTO @result SELECT ID FROM dbo.GetInheritedCells(@rowID, @ColumnRelatedToFromColumnTypeID);
END
ELSE
BEGIN
INSERT INTO @result SELECT cls.ID
FROM dbo.[Rows] r
LEFT OUTER JOIN dbo.[Columns] col
ON r.SheetID = col.SheetID
LEFT OUTER JOIN dbo.[Cells] cls
ON r.ID = cls.RowID AND col.ID = cls.ColumnID
WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID;
END
END
END
RETURN;
END
Updated RowsData view:
ALTER VIEW RowsData
AS
SELECT r.ID, s.ID SheetID, col.ID ColID, /*Columns Added*/col.ColumnSameSheetInheritFromID, col.ColumnRelatedToFromColumnTypeID,
col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
FROM Sheets s
INNER JOIN [Columns] col ON s.ID = col.SheetID
INNER JOIN [Rows] r ON s.ID = r.SheetID
/*OUTER APPLY added*/
OUTER APPLY (SELECT ID FROM dbo.GetInheritedCells(r.ID, col.ID)) inhCel
LEFT JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID /* This added -> */ OR inhCel.ID = cls.ID
LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO
Resultant data example:
ID SheetID ColID ColumnSameSheetInheritFromID ColumnRelatedToFromColumnTypeID Name NativeValue
----------- ----------- ----------- ---------------------------- ------------------------------- -------------------------------------------------- ---------------
1 1 1 NULL NULL Person Name Aron
1 1 2 NULL NULL Person Country Uruguay
2 1 1 NULL NULL Person Name Sam
2 1 2 NULL NULL Person Country NULL
3 2 4 NULL NULL Country Name Uruguay
4 2 4 NULL NULL Country Name USA
5 2 4 NULL NULL Country Name Russia
6 3 5 NULL NULL Pet Name Mickey
6 3 6 NULL NULL Pet Age 83
6 3 7 NULL NULL Pet Person Owner Aron
6 3 8 7 2 Pet Country Uruguay
7 3 5 NULL NULL Pet Name Pluto
7 3 6 NULL NULL Pet Age 8
7 3 7 NULL NULL Pet Person Owner Aron
7 3 8 7 2 Pet Country Uruguay
8 3 5 NULL NULL Pet Name Zabivaka
8 3 6 NULL NULL Pet Age 4
8 3 7 NULL NULL Pet Person Owner Sam
8 3 8 7 2 Pet Country NULL
Database script with example data to play with here!
Thanks to dfhwze who was created this fiddle to play with!
