Re: Variable tables
Date: Fri, 4 Sep 2009 10:02:47 -0700 (PDT)
Message-ID: <fc0223a9-0235-44b3-9807-c0dd49ceb2b4_at_x5g2000prf.googlegroups.com>
On Sep 4, 4:14 am, Google Poster <gopos..._at_jonjay.com> wrote:
> On Sep 4, 2:43 am, "David Portas"
>
>
>
> <REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> > "Google Poster" <gopos..._at_jonjay.com> wrote in message
>
> >news:67eb6c60-22c8-4aaf-bc98-2fb9b78249c0_at_z16g2000yqe.googlegroups.com...
>
> > > This is a continuation of my whining against the relational model.
>
> > > Yes, I realize that it is efficient and its theoretical background is
> > > elegant, nay, beautiful. I read all about it when I was in college.
>
> > > The problem is like trying to put a round peg in the proverbial square
> > > -actually: rectangular- hole.
>
> > > We deal with many different companies and have to replicate their
> > > database structure. A feature that comes to mind is the variable
> > > 'struct' in C. IIRC Oracle supports something like that. I envision a
> > > table whose fields are divided in two classes:
>
> > > - common fields, which are used by all instances of such table
> > > - a variable (or "custom" for lack of a better term) part which is
> > > specific to each case.
>
> > > I would like to re-use the programs (I program with the Pro C
> > > precompiler). across the "variant" tables.
>
> > > TIA,
>
> > > GP
>
> > All tables are "variable", ie. table structure is dynamic rather than fixed.
> > Schema change is the norm in many if not most databases and the only
> > question is how you manage that change (essentially a management problem
> > rather than a technical one).
>
> > --
> > David Portas
>
> I believe we have a time scaledifference as to what constitutes
> dynamism:
>
> - You are referring to table structure (its fields) changing from one
> year to the next. What you are essentially saying is that the only
> tables ever that remain constant are those given by God to Moses.
> People don't put those kinds immovable stuff in software (hardware
> perhaps).
>
> - I am referring to the table structure changing from a millisecond
> to the next. In a tight loop, the table is seen as having certain
> structure, and next iteration it is seen as different. That is a
> technical issue.
>
> In my C example I should have used the word "union".
>
> -GP
I work on a system that allows different customers to have different layouts, and the system adapts somewhat. The downsides are it has to do some level of lowest-common-denominator across database engines (which also means it avoids lots of bugs associated with newfangled features, as well as tuning issues), and it requires keeping additional metadata, which implies issues of synchronization and other stupidity like http://richardfoote.wordpress.com/2009/08/25/demonizing-foreign-keys-helden/ (in this case, the opposite problem - no primary keys!).
It works surprisingly well, considering, and also shows where the value lies - in the application code. Having worked on this app code and the language involved for many years, I, at least, am convinced that one should use the features of the database appropriate to your app, and not try too hard to generalize things. Simply make it easy to customize for customers, so you can amortize the costs over many customers and receive revenue for differences. You don't want to try to define everything down to the hardware layer, that's a real Cprogrammer mistaken viewpoint. You want to add some layers of abstraction, and be able to minimize the calls to the hardware layer, then optimize those. Think about the Exadata architecture.
Adding the ability to deal with table structure changing by the millisecond implies adding design constraints for technical reasons, leading to bad physical design. Stupid in multi-user situations, which most business situations that require a database are.
jg
-- _at_home.com is bogus. http://www.guardian.co.uk/technology/2009/sep/02/cory-doctorow-cloud-computingReceived on Fri Sep 04 2009 - 12:02:47 CDT