Re: Which normal form is this violating?

This design does not violate any normal forms.

This is called the vertical mapping approach to implementing object inheritance in a relational database. Table_A is the super-class and Table_B is the sub-class. You may eventually also have Table_C, Table_D, etc., that are also sub-classes of Table_A. The primary key of Table_A will ensure mutually exclusive primary key values across all sub-class tables.

Also, with this approach you put the attributes of the super/sub-class on the appropriate table. If an attribute is common to all sub-classes, you put it on the super-class table. If an attribute is specific to a single sub-class, you put it on its table. To get the all the attributes of a sub-class, you must join the super-class table with the sub-class table. To create a sub-class instance, you must insert into both the super-class table and the sub-class table.

There is also the horizontal mapping approach to implementing object inheritance. In this approach, you use only a single table to hold instances of all sub-classes. The table also includes a column that identifies the class of the instance that a row represents. Attributes that pertain to sub-classes either must be nullable or be not nullable with a default.

And finally, there is the independent mapping approach to implementing object inheritance. In this approach, each sub-class is its own table and contains all its specific attributes and the attributes of its super-class.

There are performance and complexity trade-offs with all of these approaches. And you don't necessarily have purely to follow any approach. With deep inheritance structures, you can stratify and converge super/sub-classes where it makes sense. Oracle has some nice features such as sequences and insteadof triggers that make it relatively easy to pick the implementation that fits your data usage.

"Roger Redford" <dba_222_at_yahoo.com> wrote in message news:a8c29269.0204242031.9d9964f_at_posting.google.com...
> Hello DB Design experts,
>
> I'm having the usual disputes about database design issues.
>
> The information that my coworkers have is say, x and y.
> It has a one to one relationship. Therefore, it
> goes into one and the same table.
>
> Table_A
> Fieldx (pk)
> Fieldy
>
>
> However, they are arguing that it goes into another table.
>
> Table_A
> Fieldx (pk)
>
> Table_B
> Fieldx (pk)
> Fieldy (not null)
>
> (Actually, thye have "designed" a number of strange tables,
> and then put views on top of them, to come back to the same one to
> one relationship. Very strange and complex. )
>
> What normal form does this violate? It isn't 1st,
> 2nd, or 3rd. Boyce-Codd maybe? The crazy thing about
> the design texts, is that they rarely cover mistakes
> in design. This is a common one.
>
> Thanks
Received on Thu Apr 25 2002 - 08:58:30 CDT