[AccessD] Indexes on a table from relationships

Jim,

  Thanks for the responses.  I don't actually have a child table with that
many keys, but our Person table is getting to be parent to a lot of tables.

Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Jim Dettman" <jimdettman at earthlink.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, August 13, 2003 11:08 AM
Subject: RE: [AccessD] Indexes on a table from relationships


> Mark,
>
> <<Ok, just to be clear, I could have Parent be the parent table to 40
tables
> with RI, and I wouldn't go over the limit, at least from these alone.>>
>
>   As long as it was the one of the possible 32 indexes you could have on
the
> parent side, then yes you could do that.
>
> <<However, if I tried to have 40 Foreign key fields with RI, I'd be out of
> luck, unless I split Child into some OTO tables.>>
>
>   Correct.
>
>   Generally I've only seen the limit a problem in some HR apps and
> scientific applications.  Most folks don't hit it if the DB is designed
> properly (not saying you didn't either<g>).
>
> Jim Dettman
> President,
> Online Computer Services of WNY, Inc.
> (315) 699-3443
> jimdettman at earthlink.net
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark
> Whittinghill
> Sent: Wednesday, August 13, 2003 12:02 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Indexes on a table from relationships
>
>
> Ok, just to be clear, I could have Parent be the parent table to 40 tables
> with RI, and I wouldn't go over the limit, at least from these alone.
> However, if I tried to have 40 Foreign key fields with RI, I'd be out of
> luck, unless I split Child into some OTO tables.
>
> Mark Whittinghill
> Symphony Information Services
> 612-333-1311
> mwhittinghill at symphonyinfo.com
> ----- Original Message -----
> From: "Jim Dettman" <jimdettman at earthlink.net>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, August 13, 2003 10:12 AM
> Subject: RE: [AccessD] Indexes on a table from relationships
>
>
> > Mark,
> >
> > <<1. This is how I understand indexes in Jet mdbs.  You can have up to
32
> > indexes on a table.  If you have a relationship with Referential
> integrity,
> > Access creates an index on the linked field.  If you set an index on the
> > same field, you have double indexed it.  In this case, you would want to
> > remove the index from the indexes popup, and just let the autocreated
> index
> > be the index for that field.>>
> >
> >  That is correct.
> >
> > <<2. Let's say we have two tables, Parent and Child.  Parent links OTM
on
> > Parent.ParentID (PK) join Child.ParentID.  Will this count as one of the
> 32
> > allowed indexes for Parent, Child, or both?>>
> >
> >   Child as long as RI is enforced.  If RI is not enforced, no additional
> > indexes are created.  If it is, Access/JET creates a hidden index on the
> > child side.
> >
> >
> > Jim Dettman
> > President,
> > Online Computer Services of WNY, Inc.
> > (315) 699-3443
> > jimdettman at earthlink.net
> >
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>