Re: An 1-to-1 relation: Is it normal?

From: Jim Gregory <jim.gregory_at_internetaddress.com>
Date: 1998/04/01
Message-ID: <01bd5d76$1cdfe040$6d131995@aohwhq1gregoj.DaytonOH.NCR.COM>#1/1

I have done this in some cases with good results. I look primarily at how the data will be accessed. If I have a set of data for an entity where some will be accessed frequently and some seldom, and if the seldom accessed data is not going to available anyway for a large number of the entity, I start to lean toward splitting it into 2 tables with a 1:0or1 relationship.

Even though null fields don't take up much space, there is some overhead for maintaining info about the field in the header data. Why keep it if it's not going to be used much? Splitting them can also cut down on later expansion of the row and possible chaining when the data no longer fits into the original block. More rows can be packed into a block for more efficient access The seldom used data can be put into a different tablespace if it makes sense to do so.

My point is, under certain circumstances, splitting the table can be useful. You have to analyze not only the quantity of data involved but also how it is going to be accessed.

For what it's worth
Jim Gregory

Any expressed opinions are mine and do not reflect those of my clients or employer

jeffd <jeffd_at_allensysgroup.com> wrote in article <352134DA.3493_at_allensysgroup.com>...

> Igor Sereda wrote:

> >
> > Hello,
> >
> > i am pretty confused by a sort of solution for the folling situation:
> > I have a table containing personal information with primary key
 PERS_ID.
> > There are few fields which are truly required and a lot others which
> > would be null in most cases.
> > So there's a tempting idea to separate one table into two, so one would
> > contain NOT NULL fields, indexed by primary key PERS_ID,
> > and the other will be filled with all other fields, also indexed by
> > primary key PERS_IS, also functioning ass a foreign key into the first
> > table.

>> > So what we are getting is a one-to-one-or-zero relation.

> > I feel there's something wrong in such a construction, but
> > i cannot find out what exactly.
> >
> > I will appreciate any advise,
> >
> > Thank you,
> > Igor Sereda
> >
Received on Wed Apr 01 1998 - 00:00:00 CST