Hi,

Not quite the right title, but here is the problem:

I am creating a complex module for DNN (4.9) that will act as a semi-medical research database. Part of the datamodel is:

You have parameters, parameter-templates and activities. A parameter is a definition of a name-value pair of which the value can bet numerical or text. The templates just group a set of parametes and give it a name. When adding an activity to the system it will be done by selecting a template and fetching the appropriate parameters to fill. because the users themselves can define parameters and templates i can not stuff them in ordinary tables. I do not know upfront whta the parametes and templates will look like.

I have already decided that the values (numerical or text) will be stored as nvarchar and translated in and out of their actual datatype in the app. THe question: How do i store these parameters in the activity? The activities table consistst of at least an ID, thes start date/time, the end date/time and a nvarchar(max) field for remarks. I see two options:

- an additional table with a unique ID as prim. key, activityID and ParameterID as foreign keys and the value as nvarchar

- Store the ParameterID's and the values in XML and store this in a XML datatype column in sql server (2005) in the activities table.

The amount of parameters per template are in the order of 20 - 30. This means that with option one all activities would cause the additional table to have 20 - 30 records. The second option has no additional table but qould cause 20-30 values stored in xml format.  I will probably store app. 490.000 activities each year and would like to build a histotry of 5 years. I am not worried about the querying for research as that can be done in batch.

Does anyone have compelling reasons to support either solution or to advise against either solution?

Tx,

Alexander