An alternate approach to custom fields

[Bradley Baetz]

On Fri, Mar 21, 2003 at 12:15:32PM -0500, Christopher Hicks wrote:
> 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.

We already have a fielddefs table - we'd just have to extend it a bit

> 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:
> 
> "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.

We will be using referential integrity. That said, we could probably
enforce it via a trigger, except for mysql.

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

That precludes a unique constraint when its not.

Now, this would all be simpler if we coudl store it in multiple tables,
and then have a VIEW with a UNION ALL query to bring them all together.

But....

Bradley