Custom Fields again

[Bradley Baetz]

On Wed, Dec 11, 2002 at 09:22:57AM +0000, Gervase Markham wrote:
> 
> What's wrong with "dynamically" creating tables? And what's more 
> "dynamic" about them than checksetup.pl's creation of tables? (Yes, we 
> could prefix table names with cf_ to avoid clashes.)

Firstly, its going to be massivly slow. How big is bmo's bugs table,
again? That has to be copied for each update to the schema. What happens
when teh webserver times out half way through?

> 
> >Almost. What we do is define three new tables:

<snip>

> ...and a whacking great performance hit, surely. You have to join to one 
> or more tables for every custom field you have. Also, numeric data is 
> not numeric, it's varchar, and that's bad for search performance.

It also gives us per product fields, which is something which is often
requested, although I guess we can do that.

Yes, you have to do an extra table join. As I said, I don't want mysql's
suckiness to influence this - we can do the subselect emulation like we
do for product and component. The multi value stuff then just becomes a
single lookup, with no joins involved.

numeric/varchar is sucky, I agree, though.

> 
> If we add rows to the bugs table for each (single-valued) field, this 
> has several advantages. For a start, without too much work, we could 
> make many of our current bugs tables fields into custom ones, allowing 
> admins to remove those they don't like, because their existence will be 
> the same as other custom fields. It also lets us use numbers for numeric 
> data, and other appropriate SQL types - including short varchars for 
> short data, and long varchars for long data. And I am certain search 
> performance will be better without all those joins.

See above.

> 
> We can still have an external table with the names and types of the 
> custom fields, if that makes things better. It would be nice if we could 
> just scan all the column names from the bugs table, but maybe there are 
> reasons why that wouldn't work.

It won't work. :) How do you know if a varchar is a string, or a url, or
whatever?

You also need the constraint table, remember, for mapping names to ids
for popup stuff (single or multivalued)

I dunno, maybe this isn't as bad as I'm making out. There are setups
where the bugzilla user doesn't have schema privs, but we don't support
that anyway. This would make it harder to do so, though.

Bradley