7
\$\begingroup\$

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.

Model structure

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 this Column must inherit (must be Sheet type Column).

  • ColumnRelatedToFromColumnTypeID -> This defines from which Column of the Sheet type of the above Column must this Campo get the real inherited Cell value then.

Adjusted diagram:

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!

\$\endgroup\$
  • 1
    \$\begingroup\$ Hey I wish somebody would supply an answer but that might not happen anytime soon... I see you are a member of the DBA SE and your post appears to be on topic there so maybe that would give you better responses (perhaps from people like @Brent Ozar or the like). \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Sep 25 '18 at 4:37
  • \$\begingroup\$ @SᴀᴍOnᴇᴌᴀ to migrate the question to DBA SE, should I need to delete it from here right? \$\endgroup\$ – Guillermo Subiran Sep 25 '18 at 11:44
  • \$\begingroup\$ Not necessarily... I think you could leave it here in case somebody decides to review the queries, etc. \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Sep 25 '18 at 21:37
  • 1
    \$\begingroup\$ I'll have a go with this one and play around with it this weekend, hopefully resulting in a meaningful review. \$\endgroup\$ – dfhwze Aug 9 '19 at 11:42
  • 1
    \$\begingroup\$ Here is a fiddle to play with for anyone interested in testing the sql: dbfiddle.uk/…. You can add the link in the question if you want. \$\endgroup\$ – dfhwze Aug 9 '19 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.