An alternate approach to custom fields

[Christopher Hicks]

On Fri, 21 Mar 2003, Gervase Markham wrote:
> bbaetz outlined how we would like this to work in a message on the 1st 
> of this month:

bbaetz' answer sounds good as far as storing the data, but he left out a 
table for storing this mapping information.  Having the mapping 
information in the database has always been essential in my experience.
If this information doesn't ride along with the data you have to maintain 
it as part of the code which would undermine the value of making the 
custom fields generalizations in the first place

For instance:

	ID	Name		Type	Multi	FKID
	--	----		----	-----	----
	1	dept		fk	1	1
	2	manager		email	0	NULL
	3	fixedon		fk	1	2
	4	serverroom	text	0	NULL

"ID" would be a uniqie identifier for each custom field.

"Name" would be a human-readable unique identifier.  Additionally, a 
presentable UI name would probably be beneficial, but I omitted that for 
brevity.

"Type" would indicate what kind of thing it is.  'text', 'email', 'float' 
and 'int' have been mentioned and would be basic types each with their own 
table.  'FK' would be for foreign keys.

"FKID" would identify the foreign key.  Above I've used an integer to
indicate this which would point to a portion of a codes table.  bbaetz
answer implied he'd prefer to have a seperate table for each foreign key,
so having a table name here might be necessary for his approval.  I'd
encourage having a single table for such simple code lookups however.  If
you're not using relational integrity and you're not stuck in the Orthodox
Relational perspective, it tends to lead to a lot less clutter and it
makes creating an interface to let people maintain codes easier.

"Multi" would indicate whether multiple instances of that field are 
allowed.

-- 
</chris>

The death of democracy is not likely to be an assassination from ambush. It
will be a slow extinction from apathy, indifference, and undernourishment.
-Robert Maynard Hutchins, educator (1899-1977)