Multi-Duplicates OK-Primary Keys

[Philip]

Hey all,

(Access 2000)
I've been having a horror story with this design problem. My Database is
Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
Item in each table needs a unique ID# based on its context.

Primary Keys
AUTHORS = AuthorID - NO Duplicates

BOOKS = AuthorID - Dups OK
BookID - Dups OK

PAGES = AuthorID - Dups OK
BookID - Dups OK
PageNumber - Dups OK

So for instance the pages need to number from 1 to <i>n</i> for each Book of
each Author.

The problem I had after I set this up is that from PAGES to BOOKS
I could NOT use referencial integrity because it could not identify a unique
Primary Key, (because I used Duplicates OK = True in BOOKS)

-> Is this not a valid way link a 3 level database?

I tried using auto-number primary keys but that killed the validation for
duplicates in the table.

-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the
same as one primary Key with Dups = NO so that I can set up integrity
between tables using these two primary keys?

Thanks for any insight!
~Philip

[Bas Cost Budde]

Re: Multi-Duplicates OK-Primary Keys

Philip wrote:
[color=blue]
> -> Is this not a valid way link a 3 level database?[/color]

Oh yes it is.
[color=blue]
> I tried using auto-number primary keys but that killed the validation for
> duplicates in the table.[/color]

A *very good* observation. Write it down for future reference. :-)[color=blue]
>
> -> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the
> same as one primary Key with Dups = NO so that I can set up integrity
> between tables using these two primary keys?[/color]

Nah. You have *one* primary key, which may consist of more fields, each
with its own index (duplicate or not) but the composite *MUST BE UNIQUE*
otherwise it doesn't work as primary key.

In the design view of the tables, select all rows pertaining to the key,
and only then press the 'key' button to set those as primary key.

--
Bas Cost Budde

http://www.heuveltop.org/BasCB

but the domain is nl

[Fletcher Arnold]

Re: Multi-Duplicates OK-Primary Keys

"Philip" <Philip@afterap riltax.com> wrote in message
news:WqQZb.6108 5$1S1.34054@nwr ddc01.gnilink.n et...[color=blue]
> Hey all,
>
> (Access 2000)
> I've been having a horror story with this design problem. My Database is
> Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
> Item in each table needs a unique ID# based on its context.
>
> Primary Keys
> AUTHORS = AuthorID - NO Duplicates
>
> BOOKS = AuthorID - Dups OK
> BookID - Dups OK
>
> PAGES = AuthorID - Dups OK
> BookID - Dups OK
> PageNumber - Dups OK
>
> So for instance the pages need to number from 1 to <i>n</i> for each Book[/color]
of[color=blue]
> each Author.
>
> The problem I had after I set this up is that from PAGES to BOOKS
> I could NOT use referencial integrity because it could not identify a[/color]
unique[color=blue]
> Primary Key, (because I used Duplicates OK = True in BOOKS)
>
> -> Is this not a valid way link a 3 level database?
>
> I tried using auto-number primary keys but that killed the validation for
> duplicates in the table.
>
> -> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True[/color]
the[color=blue]
> same as one primary Key with Dups = NO so that I can set up integrity
> between tables using these two primary keys?
>
> Thanks for any insight!
> ~Philip[/color]


Do you realise the difference between a primary key and a unique index? You
can have a primary key PageID which is simply an autonumber to identify the
record, yet still enforce a unique index so that you cannot have a duplicate
AuthorID, BookID, PageNumber.
While looking in the table in design view, select View>Indexes and create a
new one called idxPages then select these 3 fields, one below the other, and
set primary to 'no' but unique to 'yes'.

Will this help you do what you want?

Fletcher

[Philip]

Re: Multi-Duplicates OK-Primary Keys

Thank you for the swift response!
I was able to get the relationship working for AUTHOR-BOOK-PAGE with just a
few tweaks

[Philip]

Re: Multi-Duplicates OK-Primary Keys

> Do you realise the difference between a primary key and a unique index?
You[color=blue]
> can have a primary key PageID which is simply an autonumber to identify[/color]
the[color=blue]
> record, yet still enforce a unique index so that you cannot have a[/color]
duplicate[color=blue]
> AuthorID, BookID, PageNumber.
> While looking in the table in design view, select View>Indexes and create[/color]
a[color=blue]
> new one called idxPages then select these 3 fields, one below the other,[/color]
and[color=blue]
> set primary to 'no' but unique to 'yes'.
>
> Will this help you do what you want?[/color]

Thank you for the explanation, another small piece of my ignorance has died
at your hands, however,
I ran into an issue with the second part of the DB and your explation seemed
like it would provide the solution, but I cannot get it to work the way you
described...

The following relationship describes the same AUTHOR DB with two different
Tables

Primary Keys
AUTHORS = AuthorID - DupNO

BOOKCATEGORYS = AuthorID - DupOK
BookCatId - DupOK
BookCatOrder - DupOK

BOOKORDER = AuthorID - DupOK
BookCatId - DupOK
BookOrder - DupOK

Each Author has different categorys for his/her books, each category has a
certain list of books and an order for them. Note: The Categories also have
an BookCatOrder ID

I tried your suggestion and Made The first two Fields (AuthorID and
BookCatID) the only primary's
while I made a multi field index for the two Tables with all three fields,
so those fields would be unique.

This allowed me to create a relationship with integrity but did NOT validate
uniqueness!

I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2

Two different Categories are claiming to be the second in the Category Order
It was only validating the primary keys for uniqueness

-> Are there limitations when using index's with the primary keys on same
fields? I have never used multi-field indexes.

Thanks,
~Philip

[Fletcher Arnold]

Re: Multi-Duplicates OK-Primary Keys

"Philip" <Philip@afterap riltax.com> wrote in message
news:IYRZb.4320 0$5W3.12300@nwr ddc02.gnilink.n et...[color=blue][color=green]
> > Do you realise the difference between a primary key and a unique index?[/color]
> You[color=green]
> > can have a primary key PageID which is simply an autonumber to identify[/color]
> the[color=green]
> > record, yet still enforce a unique index so that you cannot have a[/color]
> duplicate[color=green]
> > AuthorID, BookID, PageNumber.
> > While looking in the table in design view, select View>Indexes and[/color][/color]
create[color=blue]
> a[color=green]
> > new one called idxPages then select these 3 fields, one below the other,[/color]
> and[color=green]
> > set primary to 'no' but unique to 'yes'.
> >
> > Will this help you do what you want?[/color]
>
> Thank you for the explanation, another small piece of my ignorance has[/color]
died[color=blue]
> at your hands, however,
> I ran into an issue with the second part of the DB and your explation[/color]
seemed[color=blue]
> like it would provide the solution, but I cannot get it to work the way[/color]
you[color=blue]
> described...
>
> The following relationship describes the same AUTHOR DB with two different
> Tables
>
> Primary Keys
> AUTHORS = AuthorID - DupNO
>
> BOOKCATEGORYS = AuthorID - DupOK
> BookCatId - DupOK
> BookCatOrder - DupOK
>
> BOOKORDER = AuthorID - DupOK
> BookCatId - DupOK
> BookOrder - DupOK
>
> Each Author has different categorys for his/her books, each category has[/color]
a[color=blue]
> certain list of books and an order for them. Note: The Categories also[/color]
have[color=blue]
> an BookCatOrder ID
>
> I tried your suggestion and Made The first two Fields (AuthorID and
> BookCatID) the only primary's
> while I made a multi field index for the two Tables with all three fields,
> so those fields would be unique.
>
> This allowed me to create a relationship with integrity but did NOT[/color]
validate[color=blue]
> uniqueness!
>
> I was still able to enter data like this for BOOKCATEGORYS
> AuthorId | CatId | BookCatOrder
> 1 | 1 | 2
> 1 | 2 | 2
>
> Two different Categories are claiming to be the second in the Category[/color]
Order[color=blue]
> It was only validating the primary keys for uniqueness
>
> -> Are there limitations when using index's with the primary keys on same
> fields? I have never used multi-field indexes.
>
> Thanks,
> ~Philip[/color]


Hi Philip
I did take a second read through and still didn't get it - sorry. I think
the problem is I can't quite understand the table structure (I'm not even
100% sure whether the word 'order' means 'position in a list' or 'order as
in a sales / purchase order'). Perhaps it's too late at night or perhaps
someone else can help out here, but while an AUTHORS table seems straight
forward, I can't see the sense of the structure for BOOKCATEGORYS.

Anyway, my general point was that you could have a single autonumber for
each table as the primary key. This allows you to refer to this record in
another table. If you want to keep a value, or combination of values
unique, then you can use additional indexes. For example,
[color=blue]
> AuthorId | CatId | BookCatOrder
> 1 | 1 | 2
> 1 | 2 | 2[/color]

Was your objection that BookCatOrder=2 should occur no more than once in the
table? If you need this to be the case, then have a single index that says
that BookCatOrder must be unique. Your table can contain a number of
indexes - each involving single of multiple fields and each with the ability
to be unique. Obviously only one can be the primary key, but this does not
(and perhaps should not) contain any meaningful data (ie arbitrarily
assigned autonumber).


Fletcher

[Philip]

Re: Multi-Duplicates OK-Primary Keys

Fletcher,

I apologize because it DID work exactly as you explained! I think I've been
working on this project far too long, that annexed to my lack of knowledge,
accounts for my misconceptions.

My problem below (where order = order in list btw) was because I index'd all
*three* fields
The two example records are correctly unique! I needed to index AuthorId and
BookCatOrder only.
[color=blue][color=green]
> > I was still able to enter data like this for BOOKCATEGORYS
> > AuthorId | CatId | BookCatOrder
> > 1 | 1 | 2
> > 1 | 2 | 2
> >[/color][/color]

Thank you!
~Philip

[Fletcher Arnold]

Re: Multi-Duplicates OK-Primary Keys

"Philip" <Philip@afterap riltax.com> wrote in message
news:O%z_b.525$ Xv2.522@nwrddc0 3.gnilink.net.. .[color=blue]
> Fletcher,
>
> I apologize because it DID work exactly as you explained! I think I've[/color]
been[color=blue]
> working on this project far too long, that annexed to my lack of[/color]
knowledge,[color=blue]
> accounts for my misconceptions.
>
> My problem below (where order = order in list btw) was because I index'd[/color]
all[color=blue]
> *three* fields
> The two example records are correctly unique! I needed to index AuthorId[/color]
and[color=blue]
> BookCatOrder only.
>[color=green][color=darkred]
> > > I was still able to enter data like this for BOOKCATEGORYS
> > > AuthorId | CatId | BookCatOrder
> > > 1 | 1 | 2
> > > 1 | 2 | 2
> > >[/color][/color]
>
> Thank you!
> ~Philip[/color]


Glad you got it to work. Thanks for the feedback.
Fletcher