0

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!

I did this question a long time ago in code review forum but no luck. Someone told me could be a good idea to post it here.

Code Review post link

Thanks to dfhwze who was created this fiddle to play with!

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.