Re: Too add a new table or not

The appropriate thing to do is to decide first if you want to use a commercially available, large production RDBMS. In this case you should stick to relational design and make 1 table only. The overhead of unused attributes optimization is a concern for the engine, not the designer.

In an object oriented database you would also use 1 table only, however you would make a tree of attributes depeding on the object type.

Subtyping tries to achieve the middleway between relational and object oriented design. Subtypes _do_ inherit all parent's properties (if designed correctly), however they show no polymorphism (the capability to change the initial property to something more suitable for the subtype, since any - relational - query including the parent's property, would void the subtype's property with the same name). They do also display no methods, but that's beside the point. In short, as objects, they are weak.

Let me put this simply: if a well normalized, large, datamodel (say, 500 tables) - a friend used to say that it shouldn't get past 2.75 normal form:-) - uses subtyping for any case where it may occur, it will easily bloat to 5-10 times the initial number of tables (I've worked on such 4000+ tables models, with not much more functionality then the 500 tables one), which is a developers nightmare, not to speak of the DBA. No significant functionality is added by the subtypes, as oposed to the "big tables" design. And it's not relational, any way you put it.

Greetings,
Radu
"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message news:NCVF7.14844$xS6.20479_at_www.newsranger.com...
> In article <YNBXIBKav+57EwfG_at_shrdlu.com>, Bernard Peek says...
> >
> >In message <6dae7e65.0111060408.443f57c8_at_posting.google.com>, Lennart
> >Jonsson <lennart_at_kommunicera.umea.se> writes
> >
> >>
> >>What if Student have lets say 15, and Teacher have 5 attributes. Given
> >>that 2 or 3 of the attributes are common for both types, should one
> >>model that with a single table, or use 1 parent table and 2 subtype
> >>tables?
> >
> >Database design isn't only driven by the structure of the data. You
> >might need to think about how the data is going to be used. How big are
> >the tables? If you create a new table then you will probably need to
> >write some new programs too. Can you justify the extra time to do that?
> >In a large system the extra storage space for unused fields may make it
> >worth optimising the system by creating extra tables.
> >
>
> I was more thinking about an initial design - and to make things simpler -
only
> the logical model. Lets say I have identified the structures mentioned
earlier,
> should I aim for 1 or more entities? I'm participating in a project where
we
> have similar structures, thus interested in what is considered the
apropiate
> thing to do.
>
> Kind regards
> /Lennart
>
>
Received on Tue Nov 06 2001 - 22:43:47 CET