When to use 1:1 relationships?

[Chuck Van Den Corput]

I have an application with one main table with a jillion attributes.
There are numerous other tables, but these are either children of the
main table or lookup tables.

I am finding that I am using up all my indices on the main table to
support the relationships with all the child and lookup tables.

My question is this: would anyone suggest or caution against the use
of tables that are related to the main table in a 1:1 relationship?

For example, if a number of the fields in the main table have to do
with a like topic, I could move them into their own table. Then, there
could be a 1:1 relationship between the main table and the newly
created table. While this relationship would require an index, a
number of indices would be moved from the main table to the new
ancillary table.

Chuck

[David W. Fenton]

NO************* ****@sympatico. ca (Chuck Van Den Corput) wrote in
<k0************ *************** *****@4ax.com>:

I have an application with one main table with a jillion
attributes. There are numerous other tables, but these are either
children of the main table or lookup tables.

I am finding that I am using up all my indices on the main table
to support the relationships with all the child and lookup tables.

My question is this: would anyone suggest or caution against the
use of tables that are related to the main table in a 1:1
relationship ?

For example, if a number of the fields in the main table have to
do with a like topic, I could move them into their own table.
Then, there could be a 1:1 relationship between the main table and
the newly created table. While this relationship would require an
index, a number of indices would be moved from the main table to
the new ancillary table.

I've never run up against any index restrictions.

My guess is that you want to break up your main table, but not into
1:1 relationships. My gut tells me that you've probably got a
denormalized structure somewhere in there, which would be the
quickest way to use up indexes (say you have a repeating field,
Field1, Field2, Field2, that is related to a lookup table -- the
clue here is having more than one field pointing to another table).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Chuck Van Den Corput]

>I've never run up against any index restrictions.

My guess is that you want to break up your main table, but not into
1:1 relationships. My gut tells me that you've probably got a
denormalized structure somewhere in there, which would be the
quickest way to use up indexes (say you have a repeating field,
Field1, Field2, Field2, that is related to a lookup table -- the
clue here is having more than one field pointing to another table).

Dave, thanks for your response.

I believe that the table is normalized. I just have an awful lot of
fields, too many I am guessing. I have maxed out on indices due to the
three primary uses of them:
* fields for which I'd like to ensure speedy queries
* numerous fields linked to lookup tables, for the sake of
guaranteeing referential integrity
* several fields linked to child tables

I *do* have instances with multiple fields related to the same lookup
table and would be willing to listen to ways around this. These are
*not* cases where I have been too lazy to introduce a proper 1:N
parent-child relationship (e.g., Patient1ID, Patient2ID, Patient3ID
instead of a 1:N relationship between tblDoctor and tblDoctorPatien t).
Rather, they are more of the flavour PropertyProvinc eID,
LandlordProvinc eID, ManagerProvince ID, etc., all pointing to the same
lookup table, tblProvince.

Chuck

[David W. Fenton]

NO************* ****@sympatico. ca (Chuck Van Den Corput) wrote in
<38************ *************** *****@4ax.com>:

I've never run up against any index restrictions.

My guess is that you want to break up your main table, but not
into 1:1 relationships. My gut tells me that you've probably got
a denormalized structure somewhere in there, which would be the
quickest way to use up indexes (say you have a repeating field,
Field1, Field2, Field2, that is related to a lookup table -- the
clue here is having more than one field pointing to another
table).
Dave, thanks for your response.

My name is David.I believe that the table is normalized. I just have an awful lot
of fields, too many I am guessing. I have maxed out on indices due
to the three primary uses of them:
* fields for which I'd like to ensure speedy queries
* numerous fields linked to lookup tables, for the sake of
guaranteeing referential integrity
* several fields linked to child tables
Do you know about the hidden indexes problem? They are created by
establishing relationships. These will show up in the table
documenter. Also, be careful about the auto-indexing of ID fields
(a setting in the options), which can also create unnecessary
indexes. I once reduced a 45MB data file to under 40MBs by
eliminating these duplicate indexes.
I *do* have instances with multiple fields related to the same
lookup table and would be willing to listen to ways around this.
These are *not* cases where I have been too lazy to introduce a
proper 1:N parent-child relationship (e.g., Patient1ID,
Patient2ID, Patient3ID instead of a 1:N relationship between
tblDoctor and tblDoctorPatien t). Rather, they are more of the
flavour PropertyProvinc eID, LandlordProvinc eID, ManagerProvince ID,
etc., all pointing to the same lookup table, tblProvince.

Hmm. Sounds denormalized to me. Why is information about the
landlord and manager stored in the main record? Sounds like a
separate entity that should be stored elsewhere with a link to the
main record. Actually, it's the other way around, the record in the
table you're having trouble with is the child of a landlord, a
manager. Given that a landlord might have multiple properties,
you'd want a N:N join table between the person and property table,
and the provice would be an attribute of the landlord's address,
which could certainly be stored in a separate table. Indeed, you
could take care of both landlord, manager and tenants with a join
table between your property table and your people table, with a
role code in the join. Data in that table could look something like
this:

PropertyID PersonID RoleCode
1 1 Landlord
1 2 Manager
2 1 Landlord
2 3 Tenant

And so forth (probably throwing the tenants into it is a bad idea,
as the relationship between the property and the tenant is very
different from the other relationships).

I've just eliminated at least two fields from your table.

As to indexes to speed up searches, that's tougher. I have found
that the conventional wisdom about indexes not speeding up sparsely
populated fields (i.e., small number of unique values, the classic
example being a Boolean field, which has only 2 possible values)
sufficiently to justify then is simply not as true as I'd like it
to be. I tried eliminating indexes on all fields that had more than
50% nulls and fewer than 10 unique values, and it made selects on
that field significantly slower.

But it's important to consider how often the selection on that
field will happen and whether the added wait of a table scan is
worth it.

But based on your one example, my bet is that your schema needs
work. That's what you asked about in the first place, so it sounds
like you're on the right track.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Chuck Van Den Corput]

On Tue, 06 Jan 2004 23:53:53 GMT, dX********@bway .net.invalid (David
W. Fenton) wrote:

NO************ *****@sympatico .ca (Chuck Van Den Corput) wrote in
<38*********** *************** ******@4ax.com> :

I've never run up against any index restrictions.

My guess is that you want to break up your main table, but not
into 1:1 relationships. My gut tells me that you've probably got
a denormalized structure somewhere in there, which would be the
quickest way to use up indexes (say you have a repeating field,
Field1, Field2, Field2, that is related to a lookup table -- the
clue here is having more than one field pointing to another
table).

Dave, thanks for your response.

My name is David.

I believe that the table is normalized. I just have an awful lot
of fields, too many I am guessing. I have maxed out on indices due
to the three primary uses of them:
* fields for which I'd like to ensure speedy queries
* numerous fields linked to lookup tables, for the sake of
guaranteein g referential integrity
* several fields linked to child tables

Do you know about the hidden indexes problem? They are created by
establishing relationships. These will show up in the table
documenter. Also, be careful about the auto-indexing of ID fields
(a setting in the options), which can also create unnecessary
indexes. I once reduced a 45MB data file to under 40MBs by
eliminating these duplicate indexes.

I *do* have instances with multiple fields related to the same
lookup table and would be willing to listen to ways around this.
These are *not* cases where I have been too lazy to introduce a
proper 1:N parent-child relationship (e.g., Patient1ID,
Patient2ID, Patient3ID instead of a 1:N relationship between
tblDoctor and tblDoctorPatien t). Rather, they are more of the
flavour PropertyProvinc eID, LandlordProvinc eID, ManagerProvince ID,
etc., all pointing to the same lookup table, tblProvince.

Hmm. Sounds denormalized to me. Why is information about the
landlord and manager stored in the main record? Sounds like a
separate entity that should be stored elsewhere with a link to the
main record. Actually, it's the other way around, the record in the
table you're having trouble with is the child of a landlord, a
manager. Given that a landlord might have multiple properties,
you'd want a N:N join table between the person and property table,
and the provice would be an attribute of the landlord's address,
which could certainly be stored in a separate table. Indeed, you
could take care of both landlord, manager and tenants with a join
table between your property table and your people table, with a
role code in the join. Data in that table could look something like
this:

PropertyID PersonID RoleCode
1 1 Landlord
1 2 Manager
2 1 Landlord
2 3 Tenant

And so forth (probably throwing the tenants into it is a bad idea,
as the relationship between the property and the tenant is very
different from the other relationships).

I've just eliminated at least two fields from your table.

As to indexes to speed up searches, that's tougher. I have found
that the conventional wisdom about indexes not speeding up sparsely
populated fields (i.e., small number of unique values, the classic
example being a Boolean field, which has only 2 possible values)
sufficiently to justify then is simply not as true as I'd like it
to be. I tried eliminating indexes on all fields that had more than
50% nulls and fewer than 10 unique values, and it made selects on
that field significantly slower.

But it's important to consider how often the selection on that
field will happen and whether the added wait of a table scan is
worth it.

But based on your one example, my bet is that your schema needs
work. That's what you asked about in the first place, so it sounds
like you're on the right track.

David, thanks for your comments. The example of multiple references to
the same lookup table was meant to be an example, not a snippet from
my actual database design. Your issues with my example are well
founded, though not germane to my situation.

I am aware of the hidden indices that result from establishing
relationships. Can these be done away with? If so, how? I thought they
had to live.

Chuck

[Tom van Stiphout]

On Tue, 06 Jan 2004 19:48:27 -0500, Chuck Van Den Corput
<NO************ *****@sympatico .ca> wrote:

Perhaps it would make sense to stop having us guess at your db design,
and post or make available for download an (abbreviated) version.

There are situations where Access runs out of gas. If you reach max
indexes and have a good db design (and you sound like you know more
than the basics), perhaps you need to look at another product. MSDE
ships with Access 2000 and up; that may be a logical place to start.
SQL Server allows 255 unique indexes per table.

-Tom.
<clip>

David, thanks for your comments. The example of multiple references to
the same lookup table was meant to be an example, not a snippet from
my actual database design. Your issues with my example are well
founded, though not germane to my situation.

I am aware of the hidden indices that result from establishing
relationship s. Can these be done away with? If so, how? I thought they
had to live.

Chuck

[Chuck Van Den Corput]

On Tue, 06 Jan 2004 20:18:03 -0700, Tom van Stiphout
<to*****@no.spa m.cox.net> wrote:

On Tue, 06 Jan 2004 19:48:27 -0500, Chuck Van Den Corput
<NO*********** ******@sympatic o.ca> wrote:

Perhaps it would make sense to stop having us guess at your db design,
and post or make available for download an (abbreviated) version.

There are situations where Access runs out of gas. If you reach max
indexes and have a good db design (and you sound like you know more
than the basics), perhaps you need to look at another product. MSDE
ships with Access 2000 and up; that may be a logical place to start.
SQL Server allows 255 unique indexes per table.

-Tom.

Thanks Tom. I wasn't being intentionally evasive or coy about my
database design. I was just attempting to distill my problem into an
easily recognizable, generic one, rather than have to provide a lot of
boring back story about my specific application.

I appreciate David's input and feel bad that he went into as much
detail as he did, thinking (due to my lack of clarity) that my problem
description was specific and not generic.

I don't post here very often but do certainly appreciate the time and
effort people have taken to help me.

Chuck