Database Field to contain data from a screen table

[RyanSmith]

I have written a small medical research application is asp that uses a sqlserver database to store data patient research data. To set the scene for my question; I am not sure what the term of the table/field design I have used is but I will try to explain. I have a table that I have called ref_FieldDefinition, this contains the definition of each data field I need to store patient research data for. (E.g. FieldUID, FieldLabel, FieldType, etc.). So I could define a field for example FieldLabel ="Smoker", with FieldType="T/F". I then have a seperate table that stores the data for each defined field , data_PatientData, (PatientUID, FieldUID, StoredData). (This is a simplified description of how I am constructing the db). When I present a user with an asp page for a particular patient, the page is built based on which fields are defined in the ref_FieldDefinition table and then which data is applicable from the data_PatientData table. Hence, I have no hard wired fields in my table design. This concept works fine for classic database field definitions, where you have a fields label and a field data input object (like a textbox, dropdown, checkbox, etc). Yesterday I was asked could I create a 3x3 "table" on the screen for users to enter values into. The first two rows of the table will contain numbers and the bottom row will contain the sum of each column. If I had a classic db table design I could hardwire fields like "topleft", "topmiddle", "topright" etc. I was hoping to do this in a smarter way and somehow define a "table" type field that I could dynamically build on display, as I do with my other field definition. Am I aiming too high here ? Does anyone have any thoughts on this ? Some brainstorming would be much appreciated. Cheers Ryan

[Mycroft Holmes]

I use the same design structure except I call then attributes and attribute types and link them to all sorts of things via a many to many link table. I use SQL 2008 so I make heavy use of the pivot functions. Only problem is the columns are dynamic so you need to use dynamic sql. This article may interest you Pivot two or more columns in SQL Server 2005[^]

Never underestimate the power of human stupidity RAH

[David Mujica]

I am using an application created by a vendor where they use XML tables to store "custom" grid type data that we can define. For example: Given an employee record, we want to store safety qualifications and their expiration dates. Something like "CPR", "QualifiedOn", "ExpiresOn" What the vendor has done is created an additional table called, "EMP_MATRIXDATA" KEYID MATRIX_NAME (name we give the custom grid, like "SafetyTests" MAXTRIX_DATA (datatype is an XML document) With this type of design, you can provide a very flexible method for storing all kinds of grid data. You can even build in an XSD column into the table which would hold the schema of the associated table. You will pay a price for searchability, but do some research on the topic of storing XML data in SQL server and you will be suprised how good it really is. Good luck. :cool: :thumbsup: