Individuelle Zusatzfelder für SQL-basierte Tabellen

Im Rahmen eines Softwareprojekts waren über die Jahre in der relationalen Datenbank verschiedenste riesige Tabellen entstanden, deren Attribute nur zu einem Bruchteil überhaupt gefüllt wurden. So existierten Tabellen, die um teils mehrere hundert feste Felder erweitert waren, die gerade mal von 0,05 % der Kunden genutzt wurden.
Dass diese Herangehensweise sich nicht auf einzelne Tabellen beschränkte sondern eher die Regel als die Ausnahme war, zeigt die Umsetzung der von Kunden geforderten „Zusatzfelder“ für Kunden, Mitarbeiter, Waren, uvm.

Best-practice oder haben-wir-schon-immer-so-gemacht-copy-paste?

Einen alternativen Ansatz auf Basis stelle ich hier kurz vor.

Auf Basis der üblicherweise in aktuellen SQL-DBs verfügbaren Pivot-Funktion kann diese Anlage und Ausgabe beliebiger Felder in die Hand der Anwender übergeben werden.

Dafür benötige ich lediglich eine Tabelle in welcher ich diese Informationen speichern kann.

CREATE TABLE [YourDBInstance].[dbo].[ExtensionFields](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[BaseTableName] [nvarchar](500) NULL,	-- Contains the Name of the base-table where this individual attribute is needed
	[FieldName] [nvarchar](500) NULL,	-- Contains the key-name for this attribute (will be accessable as "xyz" in the result set)
	[TableObjectID] [int] NULL,		-- The reference id to the primary key of the base-table.
	[Type] [int] NULL,			-- You could use the content-type for instance to distinguish between text vs. date, vs. datetime, vs. numeric input
	[Value] [nvarchar](max) NULL		-- The value you want to store to the combination of TableObjectID and FieldName
) 

Um nun zu einem Mit

      ,[Person_ID]
      ,[Team_ID]
      ,[SubsidiaryID]
      ...
FROM [YourDBInstance].[dbo].[Employees]

Habe ich nun die PersonalID „202“ von „John Doe“ emittelt, kann ich in der Tabelle ExtensionFields dieses neue Attribut mit seinem Wert anlegen.

INSERT INTO [YourDBInstance].[dbo].[ExtensionField]
           ([BaseTableName]
           ,[FieldName]
           ,[FieldLabel]
           ,[TableObjectID]
           ,[Value])
     VALUES
           ('Employees'
           ,'Konfektionsgröße'
           ,'Konfektionsgröße Arbeitskleidung'
           ,202
           ,'96')

Soviel zur Vorarbeit.

Nun geht es darum, die eigentlichen Stammdaten und die uns im DB-Schema unbekannten Zusatzattribute zusammen zu bringen.

Dazu erstelle ich eine Prozedur – oder einfach zunächst mal ein dynamisches SQL mit welchem ich Pivotierung und Join  erledige und die Daten im gewohnten klassisch tabellarischen Format ausgebe:

Über die Variable @BaseObjectTable wird angegeben, welche Tabelle mitsamt ihren Zusatzfeldern abgefragt werden soll. — Dabei werden alle Zu dieser Tabelle gefundenen Zusatzfelder als weitere Spalten automatisch dazu gejoint, selbst wenn diese — Information nur zu einem einzigen Datensatz angelegt wurde.

DECLARE @BaseObjectTable nvarchar(500) = 'Employee' 
DECLARE @query nvarchar(max) 
DECLARE @Columns varchar(max) 
DECLARE @ColumnNames varchar(max) 
DECLARE @table_Filters varchar(max) = '' 
DECLARE @own_Filters varchar(max) = '' 
-- Die Variablen @table_Filters und @own_Filters bieten die Möglichkeit, Filter auf die Felder der Basistabelle und der neu angelegten Attribute zu setzen. 
SET @table_Filters = 'and lastname = ''Doe'' and Firstname like ''Jo%'' ' -- 
SET @own_Filters = 'and fieldname = ''Konfektionsgröße'' and value like ''96'' ' ------------------------------------------------------------------------------------------------------------------------------------ 
Set @Columns =( SELECT distinct '['+[FieldName] + ']  [custom_'+[FieldName] + '], ' AS 'data()'   FROM [YourDBInstance].[dbo].[ExtensionField]   WHERE BaseTableName = @BaseObjectTable   for xml path('')) Set @ColumnNames =( SELECT distinct ' ['+[FieldName] + '], ' AS 'data()'   FROM [YourDBInstance].[dbo].[ExtensionField]   WHERE BaseTableName = @BaseObjectTable   for xml path('')) Set @Columns = SUBSTRING (@Columns,0,len(@Columns)) Set @ColumnNames = SUBSTRING (@ColumnNames,0,len(@ColumnNames)) IF (@own_Filters <> '')  BEGIN -- Findet die betroffenen Datensätze mit eigenen Zusatzfeldern: Set @query = 'Select BaseObjectTable.*, '+@Columns+' From  ( SELECT * FROM '+@BaseObjectTable+' WHERE 1=1 '+@table_Filters+' ) AS BaseObjectTable JOIN  ( SELECT TableObjectID AS ID, '+@ColumnNames+' FROM (SELECT TableObjectID, Value, FieldName FROM YourDBInstance.dbo.ExtensionFields WHERE TableObjectID in (SELECT TableObjectID FROM [YourDBInstance].[dbo].[ExtensionField] WHERE 1=1 '+@own_Filters+') ) AS SourceTable PIVOT ( min(Value) FOR FieldName IN ( '+@ColumnNames+')    ) AS PivotTable ) AS e ON e.ID = BaseObjectTable.ID' END ELSE -- findet alle Datensätze, unabhängig davon, ob eigene Zusatzfelder hinterlegt sind: Set @query = 'Select BaseObjectTable.*, '+@Columns+' From  ( SELECT * FROM '+@BaseObjectTable+' WHERE 1=1 '+@table_Filters+' ) AS BaseObjectTable left JOIN  ( SELECT TableObjectID AS ID, '+@ColumnNames+' FROM (SELECT TableObjectID, Value, FieldName FROM YourDBInstance.dbo.ExtensionField WHERE BaseTableName = '''+@BaseObjectTable+''' ) AS SourceTable PIVOT ( min(Value) FOR FieldName IN ( '+@ColumnNames+')    ) AS PivotTable ) AS e ON e.ID = BaseObjectTable.ID' exec (@query)

Das Ergebnis ist nun wie folgt:

Wird nun zusätzlich zur Konfektionsgröße auch noch für die Firmefeiern die Hinterlegung der Lebensmittelallergien benötigt, so kann dies durch

INSERT INTO [YourDBInstance].[dbo].[ExtensionField]
           ([BaseTableName]
           ,[FieldName]
           ,[FieldLabel]
           ,[TableObjectID]
           ,[Value])
     VALUES
           ('Employees'
           ,'Lebensmittelallergien'
           ,'Lebensmittelallergien'
           ,202
           ,'Haselnuss, Erdbeeren, Gluten')

erfolgen.
Das Ergebnis ist bei der nächsten Abfrage über die Mitarbeiter z. B.

Der Vorteil dieses Ansatzes ist, dass wir uns durch eine generische Lösung die Möglichkeit schaffen, für ALLE Tabellen im System durch den Kunden beliebige Zusatzattribute anlegen zu lassen, und dennoch die bestehenden Strukturen unseres RAPID-Application-Development-Frameworks zu nutzen. Beispielsweise selbst bei so vermeintlich unsinng zu erweiternden Datenbereichen wie der Anrede:

Auch in relationalen Datenbanken muss nicht jedes Attribut eines Objekts gleich in eine eigene Tabellenspalte geschrieben werden.

Insbesondere für Daten die eher selten benötigt werden bietet es sich an, auch mal die Pivotfunktionen moderner SQL-Datenbanken wie PIVOT und UNPIVOT  anzusehen. Attribute mit Bezug zum Hauptobjekt erlauben es problemlos Objekte nahezu beliebig zu erweitern ohne das Hauptobjekt sinnlos aufzublähen.