Dynamically create tables AND columns

flamblaster · September 11, 2018, 1:36am

I have a database with data from a web form that has a few metadata tables so form designers can add fields to different sections of the form. The metadata is saved in rows and I need to be able to create different tables for each form created. If the forms were static (i.e., no designers would create any more forms), I could create a view or function for each form and hard-code the columns per each form. Unfortunately, I can't really keep up with it (forms get created maybe 1-2 times a month, sometimes less).

I was wondering if there's a way that I could dynamically create the tables based on the Workgroup+'_'+FormName from the data below, then dynamically pivot the column names in the create statement for the tables.

The first @source table variable is an example of the metadata. The second table variable is a simple example of an incident that writes to a field from the metadata.

DECLARE @Source TABLE (SourceId INT PRIMARY KEY IDENTITY, WorkGroup VARCHAR(MAX), FormName VARCHAR(MAX), ColumnHeaderData VARCHAR(MAX), ColumnValuesData VARCHAR(MAX))
INSERT INTO @Source (WorkGroup, FormName, ColumnHeaderData, ColumnValuesData)
VALUES
 ('TSO', 'IncidentReport', 'Header', 'TSO Details')
,('TSO', 'IncidentReport', 'Details', 'EntryDate')
,('TSO', 'IncidentReport', 'Details', 'Notes')
,('CSR', 'TrackingReport', 'Header', 'CustomerFeedback')
,('CSR', 'TrackingReport', 'Details', 'IncidentDate')
,('CSR', 'TrackingReport', 'Details', 'FeedbackType')
,('CSR', 'TrackingReport', 'InvestigationSummary', 'Investigator')
,('CSR', 'TrackingReport', 'InvestigationSummary', 'InvestigatorNotes')
,('CSR', 'AbsenceRequest', 'Details', 'Requester')
,('CSR', 'AbsenceRequest', 'Details', 'RequestState')

SELECT *
FROM @Source

DECLARE @IncidentColumnData TABLE (IncidentId INT PRIMARY KEY IDENTITY, IncidentDate DATE, FormSourceId INT, ColumnData VARCHAR(MAX))
INSERT INTO @IncidentColumnData (IncidentDate, FormSourceId, ColumnData)
VALUES
('2018-01-01', 5, 'Just populating some info to show the correlation')

SELECT *
FROM @Source S
LEFT JOIN @IncidentColumnData I ON I.FormSourceId=S.SourceId

Ultimately, I need to see this pivoted into new tables dynamically. I grasp dynamic sql for columns, but I'm not sure how to dynamically create the tables based on the data set.

So for example, based on the data, 3 tables would be created dynamically:

CREATE TABLE TSO_IncidentReport (Header_TSO_Details VARCHAR(MAX), Details_EntryDate DATE, Details_Notes VARCHAR(MAX))
CREATE TABLE CSR_TrackingReport (Header_Customer_Feedback VARCHAR(MAX), Details_IncidentDate DATE, Details_CustomerFeedback VARCHAR(MAX), 
								 InvestigationSummary_Investigator VARCHAR(50), InvestigationSummary_InvestigationNotes VARCHAR(MAX))
CREATE TABLE AbsenceRequest (Details_Requestor VARCHAR(50), Details_RequestState VARCHAR(10))

Or...any other suggestions besides creating tables would be welcomed too.
Thanks!

yosiasz · September 11, 2018, 2:09am

how do you correlate/tie versions of data from one table to the other? are you using some sort of suffix or prefix on the tables? how do you handle newly required fields that do not exist in previous version of the table, or are almost all columns not required and you handle things in the middle tier or something like that.

flamblaster · September 11, 2018, 5:07am

Hi Yosiasz, sorry, I implied the table names but should have spelled it out. Basically, concatenating the workgroup and formname together would be the tablename..and ideally, newly required fields would be added with an alter statement.

I'm also thinking about a table-valued function that would iterate through hard-coded combinations and return a select statement based on the parameters that got passed from the concatenated form name(what I wrote as a table name above). I would just really like to save the data in tables for each form as the # of columns for each form is going to be different and doesn't lend itself well to storing in one place once you pivot that columns to a readable format.

AndyC · September 12, 2018, 1:58am

Honestly this is the point I'd seriously reconsider the design - you aren't going to be able to do anything useful with arbitrarily constructed tables. There won't be any real scope for referential integrity and you can't really query the data with anything but fairly arbitrary ad-hoc queries. If you really want to store it in SQL Server, I'd look to XML or JSON data in a column to represent reports.

flamblaster · September 12, 2018, 2:19am

Thanks Andy...I'm considering a different approach where I use a function or possibly a stored procedure that allows the user to select the forms data with parameters and pre-constructed columns depending on the form. We currently have 55 combinations of forms though, so there will probably be some performance problems.

yosiasz · September 12, 2018, 6:25pm

I agree 100% with Andy, SQL Server might not be the right solution for such a fast changing dynamic environment. something like a document based db might be better suited for this like MongoDb etc