Re: Is supertyping orthadox?

After coming back and Re-reading this I think I may have left out an important point which is: we need to tie 'clients' with 'services' this is why we need to bring them into one table.

Sorry for not being very clear on this.. and yes, I know I can't spell orthodox ;-) ...

kindest regards
 -=- Brett

"Brett Gerhardi" <brett.gerhardi_at_trinite.co.uk> wrote in message news:tbej6lpgfmli25_at_corp.supernews.com...
> Hi all, I am implementing a system that includes a relational database.
> Simply put it is a system to provision services. Each service has its own
> independant data structure requirement.
>
> We've had initial design discussions with a firm of consultants and during
> this came up the issue of how do we store the services data.
>
> 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.
>
> This is fine.. apart from the fact that you are limited to not having
 *any*
> relationships between any of the services, which isn't practical for some
 of
> the services. .. adding some form of flexible relationship that's easily
> queryable seems to me like reinventing the wheel of what the dbms does
> already for us .. not to mention exceptionally complex and probably slow.
>
> My suggestion was that we have a seperate set of tables representing each
> services data structure specifically defined and we link them all together
> by using a 'Services' table. ( I think after doing a little research that
> this is referred to as supertyping. Please correct me if I'm wrong). This
> was disputed as being a viable alternative.. with no real other argument
> other than it's 'unorthadox'.
>
> My main question is, is this method unorthadox?.. if it isn't (as I
 suspect)
> are there any papers (preferably online) written by respected database
> theorists that I could read and point them to. I've never actually
> implemented this method (although I have come up with the same design in a
> project that hasn't been created yet) so I cannot prove that it works and
> they have considerably more practical experience than me in this area, so
 I
> need some way of adding quantifiable credibility to my argument.
>
> The only other main argument that was raised against it is that it isn't
> true referential integrity, as you *could* have more than one set of data
 in
> the services structures for the same 'Services' table identifier.. which
 is
> true but this would be constrained at business logic level (trigger etc)
 and
> would only have performance impact on inserts.
>
> Thank you for your time reading this and for any comments.
>
> Kindest regards
> -=- Brett
>
>
Received on Tue Mar 20 2001 - 14:22:54 CET