Re: Is supertyping orthadox?
Date: Wed, 21 Mar 2001 13:46:22 -0000
Message-ID: <tbhc9g9k17nb1b_at_corp.supernews.co.uk>
Thank you all for your comments. I have responded inline.
"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).
So theoretically speaking I don't see any benifit as far as the schema changing goes, you're either creating a structure for containing data or you aren't, whether you use the built in dbms functionality for doing so or build your own.
One of the main arguments that they used for implementing this generic table design structure was so that things like user interfaces could be generated automatically as we'd be able to get the data structure definition easily.. it occurred to me afterwards that I can get exactly that information from the system tables anyway.. so that particular function could be provided by both methods.
> So, as usual, it is a solution with pro's and con's and it depends upon
> the situation if it is appropriate or not. Why you say that it has not
> place in relational design is beyond me. I don't see anything
> unrelational about it.
I think its really the fact that the design that they've come up with can't
represent any kind of referential integrity within itself as it stands.
There would have to be a significant amount of work done to enable that in
the implementation.. and at the end of that I wouldn't feel that I've
acheived anything other than creating a set of tables/triggers/stored
procedures that do what SQL server has been designed to do already.
I agree with you Jan that it is a solution with pro's and con's, however the only ones that I can see after doing a lot of thinking about it are implemention specific:
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. The amount of work that would need to be done to get a referential integrity implemented would probably demand thousands if not hundreds of thousands of services to be added to make that benifit cost effective.
On the plus side of using a supertype, the specific stored procedures referred to above could be generated automatically by whatever user interface was developed to add service structures (of which there would have to be one developed for the generic table structure anyway, if it were to have relationships within itself or creating the structures would be very prone to error). Also in favour of my proposal is the fact that this work can be done at any time in the systems lifetime rather than have to be built as part of the structure foundation.. so I think the pros for this project are heavily in my favour considering I estimate that there wont be any more than 100 services.
kindest regards
-=- Brett
Received on Wed Mar 21 2001 - 14:46:22 CET