By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,789 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,789 IT Pros & Developers. It's quick & easy.

normalization question and one to one relations

P: n/a
Hi,
I have a table named tblPeople for storing information about people.
It includes fields such as PersonID, FirstName, LastName etc..

I need to store information about measurements such as Height, weight,
etc. these will be used to fit costumes for each person.

The question is should the measurements be listed in a separate
tblMeasurments table with a one-to-one relationship to tblPeople?

Not every person will have measurements listed, but Jet does not save
empty fields so there will be no memory waste. On the other hand it is
a very separate kind of information that seems reasonable to list in a
separate "tblMeasurements" table.

it is easier for me just to keep all fields in the tblPeople.
The only draw back I can think of for keeping all fields in the
tblPeople is that if I need to show a combo or do various
manipulations with records from this table it may have a performance
toll compared with a smaller tblPeople, but I don't know if this will
be significant when dealing with just a couple of hundred or records.

I have other such potential tables that I don't know if I should
separate from the tblPeople or just keep as more fields in it.

Thanks
Gilad

May 26 '07 #1
Share this Question
Share on Google+
6 Replies

P: n/a
Baz
Keep all the fields in the one table. One-to-one relationships are
pointless.

<gi*****@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
Hi,
I have a table named tblPeople for storing information about people.
It includes fields such as PersonID, FirstName, LastName etc..

I need to store information about measurements such as Height, weight,
etc. these will be used to fit costumes for each person.

The question is should the measurements be listed in a separate
tblMeasurments table with a one-to-one relationship to tblPeople?

Not every person will have measurements listed, but Jet does not save
empty fields so there will be no memory waste. On the other hand it is
a very separate kind of information that seems reasonable to list in a
separate "tblMeasurements" table.

it is easier for me just to keep all fields in the tblPeople.
The only draw back I can think of for keeping all fields in the
tblPeople is that if I need to show a combo or do various
manipulations with records from this table it may have a performance
toll compared with a smaller tblPeople, but I don't know if this will
be significant when dealing with just a couple of hundred or records.

I have other such potential tables that I don't know if I should
separate from the tblPeople or just keep as more fields in it.

Thanks
Gilad

May 26 '07 #2

P: n/a
rkc
gi*****@gmail.com wrote:
Hi,
I have a table named tblPeople for storing information about people.
It includes fields such as PersonID, FirstName, LastName etc..

I need to store information about measurements such as Height, weight,
etc. these will be used to fit costumes for each person.

The question is should the measurements be listed in a separate
tblMeasurments table with a one-to-one relationship to tblPeople?
Unless you need/want to keep a history of changes those attributes make
the most sense in the same table.
May 26 '07 #3

P: n/a
gi*****@gmail.com wrote:
Hi,
I have a table named tblPeople for storing information about people.
It includes fields such as PersonID, FirstName, LastName etc..

I need to store information about measurements such as Height, weight,
etc. these will be used to fit costumes for each person.

The question is should the measurements be listed in a separate
tblMeasurments table with a one-to-one relationship to tblPeople?

Not every person will have measurements listed, but Jet does not save
empty fields so there will be no memory waste. On the other hand it is
a very separate kind of information that seems reasonable to list in a
separate "tblMeasurements" table.

it is easier for me just to keep all fields in the tblPeople.
The only draw back I can think of for keeping all fields in the
tblPeople is that if I need to show a combo or do various
manipulations with records from this table it may have a performance
toll compared with a smaller tblPeople, but I don't know if this will
be significant when dealing with just a couple of hundred or records.

I have other such potential tables that I don't know if I should
separate from the tblPeople or just keep as more fields in it.

Thanks
Gilad
There are two reasons I go with one-to-one. One is when the percentage of rows
that contain the information is fairly low. You say not all persons will have
measurements, but will it be half? Significantly less than half? Even though
the empty fields don't consume much drive space I would still consider a
separate table if they were going to be empty most of the time. Even then only
if the number of fields is more then just two or three. Just a few empty fields
in most rows wouldn't bother me, but half a dozen or more would likely push me
to a second table.

The other reason I use one to one tables in on our legacy IBM system. In that
system altering the design of a table is a much bigger deal than it is in our
typical Access/Jet or SQL Server world. For example we have been wanting our
order number field in our main orders tables to be longer (currently limited to
6 digits) for years, but have never made the change. The reason is that tons of
related objects and programs all have to be re-worked to deal with that change
so what takes a few seconds in other systems can be many weeks of work. In that
environment when someone needs a new field or two added to one of these legacy
tables (to support a non-legacy application) we nearly always do so in a one to
one table so we do not have to change the design of the original.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 26 '07 #4

P: n/a
Thanks so much to all the replies.
I feel better now to leave things as they are without adding extra
tables.

Rick, thanks greatly for taking the time to answer me.
As for your first reason, you didn't say why half a dozen or more
fields that belong to an identified subject would likely push you to a
second table. Is it a performance issue for example?

As for your second comment, I saw the same situation in another big
organization a couple of years ago. A university actualy. they told me
they spent millions and ten years listing contstraints in their
database, and were afraid to implement new technology for fear that
all this investment will be lost. I don't realy understand that. isn't
listing a contsraint in SQL Server a matter of a couple of seconds? If
all the logic is already there you only need to translate it to the
new technology, why not move on to SQL Server or to Oracle?

Gilad
May 26 '07 #5

P: n/a
Baz

<gi*****@gmail.comwrote in message
news:11**********************@p47g2000hsd.googlegr oups.com...
Rick, thanks greatly for taking the time to answer me.
As for your first reason, you didn't say why half a dozen or more
fields that belong to an identified subject would likely push you to a
second table. Is it a performance issue for example?
I can't answer as to why Rick has decided on a certain threshold, but in my
opinion, with man-hours costing vastly more than today's cheap and plentiful
hardware horsepower, prioritising micro-efficiencies over simplicity of
design is rarely cost-effective.
May 27 '07 #6

P: n/a
gi*****@gmail.com wrote:
Thanks so much to all the replies.
I feel better now to leave things as they are without adding extra
tables.

Rick, thanks greatly for taking the time to answer me.
As for your first reason, you didn't say why half a dozen or more
fields that belong to an identified subject would likely push you to a
second table. Is it a performance issue for example?
Nope. Having a table with 15 fields where 10 of them are mostly empty just
doesn't (to me) look like a table that properly models the real world entity
that the table should represent. It's not a black and white issue though.
Much would depend on what the tables held, how the data was used, etc..
As for your second comment, I saw the same situation in another big
organization a couple of years ago. A university actualy. they told me
they spent millions and ten years listing contstraints in their
database, and were afraid to implement new technology for fear that
all this investment will be lost. I don't realy understand that. isn't
listing a contsraint in SQL Server a matter of a couple of seconds? If
all the logic is already there you only need to translate it to the
new technology, why not move on to SQL Server or to Oracle?
I can't speak to that concern, but on the surface I agree that it sounds
like a weak reason to resist change. The legacy system I am talking about
uses "tables" in a relational database (UDB400), but it doesn't use them as
proper relational tables. They are used as "files" would have been under
older main frame type systems. The programs (Mostly RPG) that use these
tables do not (generally) use SQL or any other traditional database access
mechanisms and all objects are very highly interdependent on each other
right down to the "last design change".

For example, objects on the system have a "level" value that is a number
assigned when it is compiled and all RPG programs perform a "level check"
when executed. Any object that has been altered since the program was
compiled will cause the program to fail until it is recompiled. Of course
recompiling that program changes its level triggering a domino affect of
programs and other objects that all have to be identified and recompiled
before the system runs again. Changes can be done of course and they are,
but only with a lot more trouble, time, and expense, so they are not taken
as lightly as they would be in a more database-oriented system like we are
used to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


May 31 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.