Re: Too add a new table or not

Radu didn't answer the question, but I do agree with his assessment of the question. Maybe he has heard the question a hundred different times and it always boils down to the same basic issues.

Between "adding a new table ... as oppossed to sticking with existing tables and shoving all the new attributes in the existing tables" I am in favor of adding a new table.

I am in favor of subtyping as a data management and physical implementation technique using a RDBMS, not necessarily as a logical design.

Since I am mostly an Access developer, perhaps I can't deal with the issue by using CHECK constraints.

It is probably easier in the long run for DBA's and applications developers to manage a single main table and many subtype tables rather than one monster table.

For example, have a table "Persons" with attributes common to all persons, have a table "Teacher" related 1:1 to table "Persons" with attributes common to all persons of type=Teacher, and have a table "Student" related 1:1 to table "Persons" with attributes common to all persons of type=Student.

When you get new subtypes and you have to add a table, then do it. Just do the work, dammit, and quit bitching.
- cb

"Radu Lascae" <r.nospam.lascae_at_wanadoo.nl> wrote in message news:d_LF7.17$V27.288_at_castor.casema.net...
> Your boss is correct:-) There is nothing in relational theory that limits
> the number of fields in a table.
>
> There are however some structures where the _relevance_ of some attributes
> depends on the value of a particular attribute. For example, in a table of
> 'Persons', a person of type='Teacher' may have a relevant attribute called
> 'salary', whereas salary will not be relevant for another one of
> type='Pupil'. For this type of person, 'grade' is relevant.
>
> Some advocate creating different tables to hold the attributes 'salary'
and
> 'grade', having a 1-1 relationship with the table Persons. The technique
is
> called "subtyping" and is advocated mostly by Access developers. I am
> against it because 1-1 is not normalization, it's just the technical
> inability to use CHECK constraints in a particular RDBMS.
>
> Conclusion: your boss is correct:-)
>
> Radu
>
> "Pavs" <pavle_sturma_at_hotmail.com> wrote in message
> news:692c44aa.0111052053.2bddc0ac_at_posting.google.com...
> > I was wondering what the rule is when it comes to adding a new table
> > to a data structure as oppossed to sticking with existing tables and
> > shoving all the new attributes in the existing tables. (Assume that
> > the data remains normalised)
> >
> > My boss with way more experience then myself argues that more can be
> > done with less. "Why subject the database engine to more hits across
> > multiple tables when you can stick to the one table" he says.
> >
> > Well whats the rule?
>
>
Received on Fri Nov 09 2001 - 09:17:13 CET