How overcome limit of 32 per table on (indexes + relations)?
Richard
total number of indexes and relations? (See 'Footnote' below).
The index limit doesn't trouble me. But I have several databases under
development in which data relating to individuals (clinical patients,
as it happens) are organised into well over 32 tables. It follows that
I cannot (for example) rely upon enforced referential integrity to
delete all related records when a patient is deleted. I can of course
handle deletion in code - but that's laborious, and requires new work
whenever a table is added to the database. I suppose I could also
create a 'clone' table of the Patients table, linking each record in
the clone to the equivalent record in the Patients table proper, and
then linking related tables to the clone - for the price of one link
to the Patients table proper, I get a further 31 enforceable links.
However, this also is trovblesome: whenever a Patient is added to the
Patients table proper, code has to create a mirror entry in the clone.
Is there a well established and better solution? Or am I missing
something obvious?
Thanks,
Richard
Footnote:
I find from a message (<Oo0L9BZeBHA.1452@tkmsftngp04>) posted by
Michel Walsh that in Microsoft Access:
<<
You are limited to 32 indexes and relations, BY TABLE.
You are limited to 255 fields, BY TABLE.
>>
Annoyingly, the Access Help under 'Micorsoft Access database table
specifications' simply says 'Number of indexes in a table - 32' and
makes no mention of relations. However, if you try to create a
relationship when the limit has been reached you get the (still
misleading) message:
'The operation failed. There are too many indexes on table
'<tablename>' ...'
Douglas J. Steele
little on the high side to me.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Richard" <moggw...@btinternet.com> wrote in message
news:10b8defa.0202...@posting.google.com...
Jeff Boyce
I'm with Douglas -- I'm having some difficulty imaging a well-normalized
data model that requires 30+ related tables for a patient. If you provide a
bit more information to the newsgroup, perhaps there are alternate data
models that could help you with this issue.
Good luck
Jeff Boyce
<Access MVP>
John Vinson
wrote:
>How most neatly can one overcome the limit of 32 per table on the
>total number of indexes and relations? (See 'Footnote' below).
>
Like Jeff and Douglas, I'd be interested to see this rather
challenging data model! Any chance you could post a picture of the
data model or the relationships window on a webpage somewhere?
Just a note - when Access creates a relationship, it creates a new
index in the related table; so for enforced relationships, it's one
relationship - one index. It's very easy to get duplicate indexes on
the same field in this way; you only need one index on a foreign key
field, but if you initially define the field as being nonuniquely
indexed and then create a relationship, Access will sometimes create a
new index anyway! Check the Indexes collection in VBA to see if you
have "doubled up" on indexes.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Richard Warrington
It wouldn't even be 32, of course, if I needed some indexes on my 'primary'
table, Patients!
However, 32 does not seem to me a large number of tables needing to be
related to a primary table. In the present case, for example, there are 12
medical investigations (EEG, CT, Telemetry, ...) to which any patient might
be assigned - and each of these generates data with a quite distinct
structure: so, 12 tables there straight away.Then for each patient the
system needs to record details of Addresses, Referrals, Admissions, Surgical
procedures, Drug regimes, children, GPs, Pregnancies, Seizures (these are
epilepsy patients), aspects of seizure experience (warning, seizure,
post-ictal) ... and so it goes on: all these things have
distinctive data structures and require separate tables. There's absolutely
no doubt that around 40 tables are needed. I can't think this is
exceptional! But even if it is, my problem remains and, I'm sure, is
legitimate!
Richard
John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:pko86u01q3db0p2ot...@4ax.com...
Douglas J. Steele
12 different tables. Then, link your Patients table to that Investigations
table, rather than to the 12 different tables. This is a technique known as
Subtyping.
I'm with John: can you not post more details on a website somewhere?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Richard Warrington" <Moggw...@btinternet.com> wrote in message
news:a42t6f$2p5$1...@knossos.btinternet.com...
Richard Warrington
publishing - and I expect it shows!
Go to http://www.btinternet.com/~moggwarroo/index.htm and click on
'Relationships'
The graphic doesn't display all the fields in every table, of course - but I
hope it's possible to see (at least, if you take into account also the text
below the graphic) that the tables do record really quite different things .
In truth I think the 32 limit is quite restrictive in this case - and must
be in many another.
The subtyping technique would give me some room for manoeuvre, it's true -
but I'm not very happy about it for two reasons: first, it's really a
work-around - in the sense that there is (I think!) no intrinsic merit in
the use of the intermediate table except, perhaps, the relative ease of
finding the total number of investigations of each type; and second, I have
a couple of users of this database out there who have gone ahead (against my
counsel!) and recorded real data - so the sort of change now envisaged
involves a lot of hacking around to preserve data!
It's really another bunch of issues, but I'll mention them anyway! How do
you get users fully to specify their needs before you start (impossible, I
think, not least because what the a system might be capable of is not
immediately apparent to the user - and what might be useful is not apparent
to the developer)? And how on earth do you manage the installation of
versions of an evolving system without incurring huge time costs for the
preservation of data? How much time and effort on specification and piloting
can users be expected to put in? Etc, etc!! (Just ignore these rantings
unless they strike a chord!)
Thanks for your help and continued interest.
Richard
Douglas J. Steele <djst...@canada.com> wrote in message
news:#nhFgfWsBHA.1776@tkmsftngp03...
David Kerber
database. Maybe you should look at one of the 'big-iron' databases like
Sybase, Oracle, DB2, Interbase, etc?
--
Dave Kerber
Edit the return address by removing the ns_ before e-mailing
This message was entirely written using recycled electrons.
REAL programmers write self-modifying code, leading to:
Why there is ALWAYS one more bug (courtesy of O.D. Williams):
1. Debuging code is always much harder than writing it.
2. Therefore, if your code is as clever as you are capable of making
it, you are, by definition, inadequate to debug it.
John Vinson
<Moggw...@btinternet.com> wrote:
>Well, Doug, John and Jeff, you've driven me to my first venture into web
>publishing - and I expect it shows!
>
>Go to http://www.btinternet.com/~moggwarroo/index.htm and click on
>'Relationships'
Thanks. That helps!
>The graphic doesn't display all the fields in every table, of course - but I
>hope it's possible to see (at least, if you take into account also the text
>below the graphic) that the tables do record really quite different things .
>
>In truth I think the 32 limit is quite restrictive in this case - and must
>be in many another.
My only suggestions would be:
- If possible, consolidate the four Ans tables into one; it looks like
this has been oversplit! If your form generator doesn't allow you to
build forms based on a Query... get a better form generator. You
should be able to set up each form based on a "view" - a query
selecting the desired fields from a single table.
- Subtype the Inv* tables. A one-to-many link from Patients to
Investigations, with one-to-one links to the specific investigation
types, will save you maybe half your indices.
And you're quite right - the 32 index limit is a REAL pain. The other
poster's suggestion of moving this to SQL may be worth serious
consideration; if you install MSDE it's basically a complete version
of SQL Server and should allow you to get by this limitation.