Database dynamic tabular structure example

DatabaseExampleTabularStructure.sql

CREATE DATABASE [DatabaseModelExample];
GO
USE [DatabaseModelExample]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInheritedCells]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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


GO
/****** Object:  Table [dbo].[Cells]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cells](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ColumnID] [int] NOT NULL,
	[RowID] [int] NOT NULL,
	[RowValueID] [int] NULL,
	[NativeValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_Cells] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Columns]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Columns](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SheetID] [int] NOT NULL,
	[NativeTypeID] [int] NULL,
	[SheetTypeID] [int] NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ColumnSameSheetInheritFromID] [int] NULL,
	[ColumnRelatedToFromColumnTypeID] [int] NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Native_Types]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Native_Types](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_Native_Types] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Rows]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rows](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SheetID] [int] NOT NULL,
	[Identificator] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_Rows] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Sheets]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sheets](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](250) NULL,
	[WhenIsTypeShowThisColumnIDValue] [int] NOT NULL,
 CONSTRAINT [PK_Sheet] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  View [dbo].[RowsData]    Script Date: 13/6/2018 17:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[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
SET IDENTITY_INSERT [dbo].[Cells] ON 

INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (1, 1, 1, NULL, N'Aron')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (2, 2, 1, 3, NULL)
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (4, 1, 2, NULL, N'Sam')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (8, 4, 3, NULL, N'Uruguay')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (9, 4, 4, NULL, N'USA')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (10, 4, 5, NULL, N'Russia')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (11, 5, 6, NULL, N'Mickey')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (12, 6, 6, NULL, N'83')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (13, 5, 7, NULL, N'Pluto')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (14, 6, 7, NULL, N'8')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (15, 5, 8, NULL, N'Zabivaka')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (16, 6, 8, NULL, N'4')
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (17, 7, 6, 1, NULL)
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (18, 7, 7, 1, NULL)
INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (19, 7, 8, 2, NULL)
SET IDENTITY_INSERT [dbo].[Cells] OFF
SET IDENTITY_INSERT [dbo].[Columns] ON 

INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (1, 1, 1, NULL, N'Person Name', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (2, 1, NULL, 2, N'Person Country', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (4, 2, 1, NULL, N'Country Name', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (5, 3, 1, NULL, N'Pet Name', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (6, 3, 2, NULL, N'Pet Age', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (7, 3, NULL, 1, N'Pet Person Owner', NULL, NULL)
INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (8, 3, NULL, NULL, N'Pet Country', 7, 2)
SET IDENTITY_INSERT [dbo].[Columns] OFF
SET IDENTITY_INSERT [dbo].[Native_Types] ON 

INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (1, N'Text')
INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (2, N'Number')
INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (3, N'Boolean')
SET IDENTITY_INSERT [dbo].[Native_Types] OFF
SET IDENTITY_INSERT [dbo].[Rows] ON 

INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (1, 1, N'Person A')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (2, 1, N'Person B')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (3, 2, N'Country A')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (4, 2, N'Country B')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (5, 2, N'Country C')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (6, 3, N'Pet X')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (7, 3, N'Pet Y')
INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (8, 3, N'Pet Z')
SET IDENTITY_INSERT [dbo].[Rows] OFF
SET IDENTITY_INSERT [dbo].[Sheets] ON 

INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (1, N'Persons', 1)
INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (2, N'Countries', 4)
INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (3, N'Pets', 5)
SET IDENTITY_INSERT [dbo].[Sheets] OFF
ALTER TABLE [dbo].[Cells]  WITH CHECK ADD  CONSTRAINT [FK_(Cells)_(Columns)_BelongsTo] FOREIGN KEY([ColumnID])
REFERENCES [dbo].[Columns] ([ID])
GO
ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Columns)_BelongsTo]
GO
ALTER TABLE [dbo].[Cells]  WITH CHECK ADD  CONSTRAINT [FK_(Cells)_(Rows)_BelongsTo] FOREIGN KEY([RowID])
REFERENCES [dbo].[Rows] ([ID])
GO
ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Rows)_BelongsTo]
GO
ALTER TABLE [dbo].[Cells]  WITH CHECK ADD  CONSTRAINT [FK_(Cells)_(Rows)_Value] FOREIGN KEY([RowValueID])
REFERENCES [dbo].[Rows] ([ID])
GO
ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Rows)_Value]
GO
ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_(Columns)_(Columns)_ReferenceFromColumn] FOREIGN KEY([ColumnSameSheetInheritFromID])
REFERENCES [dbo].[Columns] ([ID])
GO
ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Columns)_ReferenceFromColumn]
GO
ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_(Columns)_(Columns)_ReferenceToColumn] FOREIGN KEY([ColumnRelatedToFromColumnTypeID])
REFERENCES [dbo].[Columns] ([ID])
GO
ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Columns)_ReferenceToColumn]
GO
ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_(Columns)_(Native_Types)_Type] FOREIGN KEY([NativeTypeID])
REFERENCES [dbo].[Native_Types] ([ID])
GO
ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Native_Types)_Type]
GO
ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_(Columns)_(Sheets)_BelongsTo] FOREIGN KEY([SheetID])
REFERENCES [dbo].[Sheets] ([ID])
GO
ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Sheets)_BelongsTo]
GO
ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_(Columns)_(Sheets)_Type] FOREIGN KEY([SheetTypeID])
REFERENCES [dbo].[Sheets] ([ID])
GO
ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Sheets)_Type]
GO
ALTER TABLE [dbo].[Rows]  WITH CHECK ADD  CONSTRAINT [FK_(Rows)_(Sheets)_BelongsTo] FOREIGN KEY([SheetID])
REFERENCES [dbo].[Sheets] ([ID])
GO
ALTER TABLE [dbo].[Rows] CHECK CONSTRAINT [FK_(Rows)_(Sheets)_BelongsTo]
GO
ALTER TABLE [dbo].[Sheets]  WITH CHECK ADD  CONSTRAINT [FK_(Sheets)_(Columns)_ColumnToShowValue] FOREIGN KEY([WhenIsTypeShowThisColumnIDValue])
REFERENCES [dbo].[Columns] ([ID])
GO
ALTER TABLE [dbo].[Sheets] CHECK CONSTRAINT [FK_(Sheets)_(Columns)_ColumnToShowValue]
GO