Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 15th, 2005, 07:11 PM
Authorized User
 
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default One to one relationship query

Hi all,

Just wondering on what people's thoughts are on this problem.

I currently have a table with about 70+ fields in it, and now I have hit the 32 index limit on the table.

So my two options are to:

a) create another table and have a one to one relationship with it. Pros: This will allow me to overcome the 32 index limit.
Cons: I will have to join these two tables together for any query and all existing queries and forms will need to be changed to reflect this new table

b) have the one table.
Pros: no changes to current application
Cons: unable to reinforce referential integrity on all the foreign key joins

Even though it seems like it's going to be a bit of extra work, I am favouring option a, but are there any other pro's and con's of either method?
Has anyone else come across a similar problem and what did they do?
What would you do?


Cheers

Rohan
__________________
Cheers

Rohan
 
Old August 16th, 2005, 08:34 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Why would you need sooooooo many indexes?
I would get rid of some of the indexes, perhaps all but a few.

mmcdonal
 
Old August 16th, 2005, 08:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can enforce referential integrity without creating an index on the FK. Indexes are only for searches, not referential integrity. If you add additional indexes on FKs, this is for sorting on those FKs for searches. So the Con of not enforcing referential integrity is invalid.

This makes me think that perhaps you shouldn't need so many fields in this table. What is the normalization of this schema?

mmcdonal
 
Old August 16th, 2005, 10:01 PM
Authorized User
 
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I actually don't need that many indexes, probably about 5-10 for myself. However, whenever Access creates a link and enforces RI between two tables it creates a hidden index that is counted towards the 32 limit. As I have over 30 lookup tables to this table, I go over the 32 index limit.

Unless I have got this wrong, but I'm sure I don't. If I remove RI from some of the tables then I can create more indexes that I want. However as this main table has 32 joins to other tables with RI enforced, I reach the 32 index limit.

Unless there is a way to turn it off, but I don't think that there is.

Any further comments on this and the original question from anyone?

Cheers

Rohan




Similar Threads
Thread Thread Starter Forum Replies Last Post
One To One RelationShip prasanta2expert SQL Language 0 November 7th, 2006 06:30 AM
O/R mapping many to many relationship popoxinhxan BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 August 17th, 2006 11:18 PM
one-to-many relationship sample matfis Classic ASP Databases 2 March 18th, 2005 11:37 AM
Many To Many Relationship samersult BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 1 January 26th, 2005 07:09 AM
sql:relationship Yehuda SQL Server 2000 2 December 22nd, 2004 06:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.