Re: Is supertyping orthadox?
Date: 21 Mar 2001 14:32:35 GMT
Message-ID: <99ae23$f5a$1_at_news.tue.nl>
Brett Gerhardi wrote:
>
> "Jan Hidders" <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message
> news:999qcj$9k5$1_at_news.tue.nl...
> > Kristian Damm Jensen wrote:
> > > JRStern wrote:
> > > >
> > > > On Tue, 20 Mar 2001 12:28:04 -0000, "Brett Gerhardi"
> > > > <brett.gerhardi_at_trinite.co.uk> wrote:
> > > > >
> > > > >The solution they came up with was what they termed as a
> > > > >'generic table structure' where basically there is a 'schema'
> > > > >table that stores the Field name, data type, length, (also has
> > > > >a foreign key to a 'tables' table to group the schemas
> > > > >together). Also there is a 'values' table that has a foreign
> > > > >key to the 'schema' table that in the implentation would have
> > > > >a sql_variant field that stored the data.
> > > >
> > > > See if these guys can even recite the definitions of the first
> > > > three normal forms. They can't. Fire them.
> > >
> > > Agreed. What they propose sounds like a Pascal variant-record or
> > > a C-union. It has no place in relational design.
> >
> > Not agreed. Yes, it is inefficient. Yes, it makes specifying and
> > maintaining database constraints very difficult if not impossible.
> > But it is a nice solution if you want a very flexible schema that
> > allows you to record extra properties without changing the schema.
>
> I think we may have to be careful here as there are effectively 2
> 'schema's' now in this situation. You have the hard definition of the
> schema that is represented by the physical table relationships, but
> with this design you then have a 'schema table'. So while, if you
> decide you need to add/change/remove fields, you can do it without
> changing the physical table relationships, you would need to add a
> new 'schema' record. This in my mind has no advantages.. it just
> 'hides' the issue that you've added a table - because, in effect, you
> have. This seems to me more dangerous than just creating a new set of
> tables with the changes and having a version system where you'd have
> the two structures run side by side until the data can be transferred
> into the new 'version' (if needed).
Just to be clear, I think that in your case the generic-table solution is definitely not a good solution and I would describe it as 'sloppy design' at best. As far as you remarks above concern, I agree with you completely. In order to make this design work you really have to know what you are doing because you are taking over some responsibilities that are normally left to the DBMS.
> With the generic table design, significant initial investment is
> required. In return, adding a service would need slightly less
> resources. The only thing that would be saved would be the time to
> create the stored procedures specific to that data structure (as
> they'd be using generic ones) to add/update/delete.
Well, if you do it right then the whole application will be designed generically so that adding a table doesn't need any programming at all. But again, only real experienced developers that are not afraid of some serious abstract thinking can accomplish that.
kind regards,
-- Jan HiddersReceived on Wed Mar 21 2001 - 15:32:35 CET