When to Create Separate Tables?

[Aaron G]

Am I hurting my db by creating separate tables for data in a one-to-one
relationship? The reason I did so was because I was thinking that I was
making it easier by grouping my data into related tables. Now as my
relationships are getting more and more complex, I'm wondering if I would do
better to consolidate foreign tables into the master table when they have a
one-to-one relationship.

How does this affect performace, integrity, etc? What are the "rules" on
this for good db design?

Thanks!

Aaron G
Philadelphia, PA

 

[Rick Brandt]

Am I hurting my db by creating separate tables for data in a
one-to-one relationship? The reason I did so was because I was
thinking that I was making it easier by grouping my data into related
tables. Now as my relationships are getting more and more complex,
I'm wondering if I would do better to consolidate foreign tables into
the master table when they have a one-to-one relationship.

How does this affect performace, integrity, etc? What are the
"rules" on this for good db design?

Thanks!

Aaron G
Philadelphia, PA

My usage would be dependent on how often the fields were not used. For
example if I had a properly normalized table with 20 fields but 7 of those
fields were only used in special circumstances (a low percentage of the
time) I might put those in a separate table with a 1-1 relationship so I
don't have large numbers of rows in a single table with all of those fields
Null.

I would NOT break up a table just to organize the fields into logical
groups.

 

[Dirk Goldgar]

Am I hurting my db by creating separate tables for data in a
one-to-one relationship? The reason I did so was because I was
thinking that I was making it easier by grouping my data into related
tables. Now as my relationships are getting more and more complex,
I'm wondering if I would do better to consolidate foreign tables into
the master table when they have a one-to-one relationship.

How does this affect performace, integrity, etc? What are the
"rules" on this for good db design?

Thanks!

Aaron G
Philadelphia, PA

The mere fact of the one-to-one related tables doesn't have any effect
on integrity, though it may open the door for errors on your part in
building queries, etc. Performance-wise, it would probably have some
small effect, since the database engine has to perform a join whenever
you want information from more than one table.

Much more important than that, though, is the question of whether you
are accurately representing the logical structure of the problem domain;
that is, the real-world entities and transactions you are modelling. If
you are separating entities' attributes into multiple tables just for
neatness' sake, when actually these attributes all apply to every entity
of this type, then you are probably making a mistake. On the other
hand, if your one-to-one relationship represents a subclassing of the
main entity, so that the information in the related table applies only
when the "main" entity is of a particular type, then your table design
with the one-to-one relationship is probably correct. Note that
practical considerations may apply here -- if there are just one or two
fields related to the subclass, then you might just as well save them in
the main table, with no harm done. Or if a set of fields are likely
either to be all present or all absent for any main record, then you'll
save space by breaking them out into a separate table.

Without knowing more details of your problem domain, that's all I can
say.

 

[Aaron G]

Thanks Rick. I think I'll follow your method. The extra table is indeed
fields that will only be related to a small percentage of the master table.

Just curious... would the null fields in the master db cause performance
issues? Also, what are the reasons for not doing tables according to groups?
Is it simply becuase more tables = more chances to make mistakes and a mess,
or are there performance issues here?

Thanks again.

Aaron G
Philadelphia, PA

 

[Aaron G]

Dirk,

It sounds from what you both are saying that I should have them separate.

The db is used for a financial planning firm. The master table is
"Clients". It contains the client's name, contact info, etc. Basic stuff.
Now, I want to be able to see which clients are being worked on and where
they are in the process. So I created a second table called "Status". This
one-to-one relationship table has less than 10 fields. Basically, it has a
few text boxes and two memo fields. It will only be used for clients that
are "in the process". No more than a dozen max. The rest of the clients
will be null fields.

Thanks for all your input.

Aaron G
Philadelphia, PA

 

[Rick Brandt]

Thanks Rick. I think I'll follow your method. The extra table is
indeed fields that will only be related to a small percentage of the
master table.

Just curious... would the null fields in the master db cause
performance issues? Also, what are the reasons for not doing tables
according to groups?

Probably not. I'm just anal about stuff like that.

Is it simply becuase more tables = more chances
to make mistakes and a mess, or are there performance issues here?

More joins to get stuff done is definitely a performance issue, but most
likely not one that would be that noticeable. To me it's more of a KISS
approach.

I will also utilize what our AS400 guys would call a "trailor file" when
some manager asks for a few new fields in a "mature" table and I know that
changing the structure of the table means having to recompile or redesign a
lot of Logicals, Views, and RPG programs that reference the table. By
putting the new fields in a separate table I don't affect any of those
existing structures.

 

[Aaron G]

Gottcha. Thanks again.

Aaron G
Philadelphia, PA