473,289 Members | 2,155 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

When to use 1:1 relationships?

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
Nov 12 '05 #1
6 1930
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
Nov 12 '05 #2
>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 tblDoctorPatient).
Rather, they are more of the flavour PropertyProvinceID,
LandlordProvinceID, ManagerProvinceID, etc., all pointing to the same
lookup table, tblProvince.

Chuck
Nov 12 '05 #3
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 tblDoctorPatient). Rather, they are more of the
flavour PropertyProvinceID, LandlordProvinceID, ManagerProvinceID,
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
Nov 12 '05 #4
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
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 tblDoctorPatient). Rather, they are more of the
flavour PropertyProvinceID, LandlordProvinceID, ManagerProvinceID,
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
Nov 12 '05 #5
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
relationships. Can these be done away with? If so, how? I thought they
had to live.

Chuck


Nov 12 '05 #6
On Tue, 06 Jan 2004 20:18:03 -0700, Tom van Stiphout
<to*****@no.spam.cox.net> wrote:
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.


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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: crispy | last post by:
This is something that I am never completely sure about: When building an Access db to be accessed by ASP, do I need to worry about setting the Relationships between the tables and the Join Types...
2
by: James Ankrom | last post by:
Why does this fail? Dim relResources As New Data.DataRelation("Application_Resources", ..Tables("User_Applications").Columns("Application_id"),...
20
by: Ed | last post by:
I am running Access 2002 and just ran the built in Access wizard for splitting a database into a back end (with tables) and front end (with queries, forms, modules, etc.). After running the...
22
by: Bradley | last post by:
Has anyone else noticed this problem? I converted the back-end to A2000 and the performance problem was fixed. We supply a 97 and 2000 version of our software so we kept the backend in A97 to make...
49
by: Mal | last post by:
Hi, As I gain knowledge through a lot of trial, error, and usenet posts.. I have a potentially odd question. I am using a commercial access application. It is a front-end / back...
2
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the...
3
by: Tom van Stiphout | last post by:
I have written some code to export all objects to text files, and to import those files back into a new database. Hopefully this will eliminate some forms of corruption. The icing on the cake...
11
by: Nemisis | last post by:
Hi everyone, sorry if this post gets really long, i just wanna make sure i fully explain what i am trying to do. I am new to OOP and .net 2.0, so if this is obvious, i am sorry. I have wrote a...
4
by: sloney | last post by:
Hello, I have approximately 20 mdb's that have the same schema but all have different data for different geographic locations. There are a plethora of tables and relationships (to be built). I...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.