Hello,

I am designing a prototype for a system which basically allow users to
design their own data collection tables (with some constraints), and
then populate and run queries on those tables. Eventually it will
have a HTML-based front end, and the users will be very non-technical.
Most of the queries, for example, will be pre-defined and the user
will just press a button to generate a report on their data.

In order to construct a decent UI, I envisage that there's a certain
amount of metadata I will want to associate with each column. While I
might be able to name a column 'warfarin_dose', for example, and make
it of type 'int', I need somewhere to store some metadata, such as the
definition of the data (say, 'Last recorded warfarin dose prior to
surgery'), maybe the units of the dose, and whether this is a factor
contributing to the outcome of the user's study or whether it is an
outcome.

I am using MySQL. So far I have the application running in a single
database with a master table containing some metadata on each of these
individual studies. Each study then gets its own table (which seems
like a hard requirement since each study collects potentially quite
different data), with its study number encoded into the name of the
table (which itself strikes me as a bad idea). I don't yet have a
solution for the column metadata problem.

Can anyone suggest an approach here?


--
Paul.

Logic Squad -- Technical Publishing with XML -- http://logicsquad.net/

On Wed, 25 Feb 2004 07:51:13 -0800, Tom Hester <$$tom (AT) metadata (DOT) com> wrote:

Ideally both. For example, I might want to display the string 'Last
recorded warfarin dose prior to surgery' next to the input field in
the HTML, but I might also want to store plausible lower and upper
limits for validation, and I want to know which of several broader
groupings (e.g. contributing factor or outcome) the column belongs to.

My first thought was to have a table whose columns were, say,
'full_description', 'units', 'lower_limit', 'upper_limit' and so on
and whose rows referred to a column in the main data collection table.
This idea seems fragile to me---I would have to be very careful about
maintaining synchronisation between this metadata table and the main
table.

Is there a better way?


--
Paul.

Logic Squad -- Technical Publishing with XML -- http://logicsquad.net/

I propose you consider having some metadata with your metadata in a 3
table "generic" arrangement similar to the following...

Study Table Metadata Metadata Table
StudyID int MetadataID int
StudyDescription varchar(8000) MetadataType char(5)
MetadataDescription varchar(500)
MetadataUnit of Measure
varchar(50)
MetadataHTMLDescription ...
MetadataStyleSheetName ...
MetadataHasALowAndHighRange
boolean
...

StudyMetadata
StudyID int
MetadataID int
StudySub
MetadataValue varchar(800)
MetadataRangeLow numeric
MetadataRangeHigh numeric
...

....or some such generic design could accomodate numerous "studies"
with a single data structure...of course the queries get a bit more
"interesting", but that could be handled with some clever database
view definitions.

Best,
D.McMunn

"Paul A. Hoadley" <paulh (AT) logicsquad (DOT) net> wrote

On 26 Feb 2004 22:00:26 -0800, D.McMunn <dmcmunn (AT) yahoo (DOT) com> wrote:

So StudyMetadata.MetadataID links each different study to a row in the
metadata table? The problem is that each study will want to collect a
range of data items, and it is to each of these data items that I want
to attach some metadata---i.e. to each column in each study's data
collection table. Does your scheme allow for this?

Meanwhile, I have thought of another possibility. The _amount_ of
metadata that I need for each column is not huge---just a text
description, maybe what 'subheading' it should come under in the data
entry form, units, and so on, as described earlier. I am currently
doing the prototype with MySQL, but I can easily change to
PostgreSQL---the latter having a 'comment' feature for attaching a
text comment to any database object including columns. I could just
encode this metadata into a text comment.


--
Paul.

Logic Squad -- Technical Publishing with XML -- http://logicsquad.net/

That ought to be in the data dictionary and there are tools for that
sort of thing out there already. Have you read the ISO-11179 Metadata
Standards? Have you looked at the WhiteMarsh tool based on it?

On 27 Feb 2004 10:23:17 -0800, --CELKO-- <joe.celko (AT) northface (DOT) edu> wrote:
I have not read ISO-11179 and I have not heard of WhiteMarsh. I don't
need anything terribly heavyweight. I just need somewhere to put a
few fields of metadata for each column. And I don't know what the
metadata is in advance---the user creating the study will supply it.


--
Paul.

Logic Squad -- Technical Publishing with XML -- http://logicsquad.net/