An alternate approach to custom fields
bbaetz at acm.org
Fri Mar 21 22:18:25 UTC 2003
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
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.
More information about the developers