Re: [GeneX-dev] Controlled Vocabs in DB

[<ja...@op...>]

"Todd Peterson" <tf...@nc...> writes:

> Having a separate table for each control vocabulary is extremely
> tedious. I propose the following table structure:
> 
> table: 
>     ControlVocabulary
> attributes:
>     vocabulary_name varchar(80) NOT NULL
>     term_string varchar(48) NOT NULL
>     description text
> 
> Advantages: less tables which all have the same structure. less
> maintenance. generic. code simplification.  
>
> Disadvantages: code changes in DB2XML, XML2DB, control bundle
> generator. GeneXML should NOT change.


Here's what I'm proposing:

CREATE TABLE ControlledVocab
(
	cv_pk			serial PRIMARY KEY,
	last_updated		datetime NOT NULL,
		--when the row was last modified
	last_updated_user	name NOT NULL,
		--who last modified the row
	sec_fk	int4 NOT NULL REFERENCES Security(sec_pk),
		--specifies all access and update permissions for the data

	vocab_name		varchar(128),
		--the combination of table_name and column_name used to
		--describe the vocab. Explicitly stating this helps map
		--to the XML definition file, as well as identifying
		--all terms from from the same vocab
	table_name		varchar(128),
		--the DB table this term describes
	column_name		varchar(128),
		--the exact column DB table this term describes
	term_name		varchar(128),
	description		varchar(128)
)

The last_updated, last_updated_user, sec_fk are things that almost all
tables have gotten.

I'm not sure about using a primary key, so I'd like feedback.

Having vocab_name is important, having table_name and column_name are
not strictly necessary, but I think its nice.

jas.