Re: Denormalization question, history+ current

Eric,

I don't think I can help you. Your application is well beyond the
bounds of generic advice.

-Josh Berkus

> > Your question is somewhat involved. I'm confused. What's
> "abstract
> > core" and why does it have hundreds of columns (IMHO, any table
> with
> > more than a few dozen columns is probably a result of poor
> > normalization)? Why do your column names have to be dynamic? I
> think
> > we need more detail, with at least partial table definitions.
>
> Abstract core is an abstract base class table where all of the
> columns
> that are user dependent are managed for the other tables that require
>
> this set of fields. There are at least a few that aren't shown in the
>
> previous message.
>
> Essentially, what I have is:
>
> Event = set of field/value pairs, some system defined, most user
> defined.
> History = ordered sequence of events for a pkey.
> Current View = Current value of all name value pairs for one primary
> key.
>
> The possible set of fields are defined by users of the system. That's
>
> the killer.
>
> Events end up having tens of columns with data and the rest null.
> The
> current view for an active key will approach having all of the fields
> filled
> as time goes by.
>
> This could be stored as a series of triples, eventId:field:value. In
> fact, I
> have a sample implementation of this where I can generate anything I
> would need from this, at the cost of most interesting queries being 3
>
> or 4 way self joins. For a small system, I'd be seeing millions of
> rows
> in this model, and performance is not acceptable. I can get around
>
> that by wasting (a bunch of) space.
>
> I'm at the point where I know that the many column model is the worst
>
> option, except for all the others.
>
> eric
>
>
>

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco