Re: Too add a new table or not

My appologies, I used to be an Access developer myself - it appears I forgot some facts. Not my intention to bash Access.

You may be able to deal with the issue to some extent using field validation rules instead of CHECK constraints in Access. I don't know the last versions of Access, rowlevel validation may also be an option, which would really amount to a CHECK constraint. Really, I don't know.

However, you did misunderstand what I mean, as my answer was plain enough: don't add tables when no extra normalization would be achieved, add fields.

Even from a non-theoretical point of view this makes sense. Consider a 2000+ tables, highly normalized ERP system (and that's an average, I've seen bigger). Even a superficial analysis of some of the transactional tables, like companies and addresses proves that subtyping will double the number of tables, increasing complexity. I don't see the gain for either performance, the developer or the DBA. Agreed, some space will be redudant, as rows will sometimes contain many NULL valued fields. Not something to worry with modern RDBMS or hardware.

Cheers,
Radu
"Carlos Bromanski" <cbroman_at_shpamcore.com> wrote in message news:3beb905e$0$35622$1dc6e903_at_news.corecomm.net...
> 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 Sat Nov 10 2001 - 09:40:54 CET