Composite Key or autonumber key with unique index ?

[jaryszek]

Hello Guys,

i have model where i have composite keys (primary keys - unique ones).
And what i need to do is creating relationships between tables.

What i will get i will get just flat files with composite keys specified and will have to join them to see only structure in diagram.
It is good to use Composite primary key and join it with it ?

Best,
Jacek

 

[CJ_London]

better to use a composite index to prevent duplicates, then use an autonumber primary key.

I presume your example is just that, a very simple example. Reason is there seems little point in having a one to one relationship, just include your table2.fielddiff field in table1. There are very few occasions where a one to one relationship is required.

 

[jaryszek]

Thank you CJ_London.

But composite index can i see on diagram? Or only with primary keys like i have ?
Why is better to use composite index and how to relate it ?

Yes this is just example where i created composite primary keys...
Jacek

 

[CJ_London]

in table design, on the ribbon you will see a button for indexes

to create a composite index, see how your composite primary key has been created. It might look something like this after you change it

you can name indexes what you like and set properties such as primary, ignore nulls and change the sort order. Use the last two properties to fine tune your index performance when you have large datasets (although I make a habit of considering how the app will be using the indexes regardless).

Use ignore nulls if the field is not populated very often. This reduces the size of the index and therefore the time it takes to search it.

Sort order may be relevant for something like dates e.g. if you are looking normally searching for current dates, set the sort order to descending.

 

[jaryszek]

Thank you very much.

So you recommending creating PK as autonumber and indexes?
But how i can relate this indexes each other?

And why this is better than javing multiple primary key?

Jacek

 

[CJ_London]

you do not relate indexes, you relate fields. With very few exceptions, I would always use an autonumber as a PK as its sole purpose is to uniquely identify a record.

as to why - it is simpler and easier to manage.

 

[jaryszek]

Interesting approach. Thank you.
Let's see if more people think like that and if the specific set up is faster or have impact on performance.

 

[jdraw]

I agree with CJ. Here's a link describing composite unique index.

 

[Uncle Gizmo]

Interesting approach. Thank you.
Let's see if more people think like that and if the specific set up is faster or have impact on performance.

Chris, (CJ London) is one of the best and most experienced Access developers, take heed as to his advice.....

 

[jaryszek]

Thank you Guys.

Pat, awesome explanation.

This statement i want to discuss in details:

you do not relate indexes, you relate fields.

So in my example it would be like:

this is corret approach?

With very few exceptions

Can you please provide example ?

Jacek

 

[Cronk]

Pat, your table1 does not provide for combinations of fld1 and fld2.

Long time ago, I was commissioned to develop a classifying system for photos of plants where the user wanted to record genus, species, subspecies where a requirement was that there could not be records with the same values of all three fields. It was possible for multiple records having the same genus and species but each having in that case, a unique subspecies.

Accordingly, I had a unique composite key of the three IDs.

The three IDs were FKs into 3 related tables for genus, species and subspecies names.

 

[jaryszek]

thank you very much ! I am understanding this very nice right now!

Jacek

 

[The_Doc_Man]

Let's see if more people think like that and if the specific set up is faster or have impact on performance.

OK, I'm tossing my hat into the "autonumber PK" for linking parent/child tables and use restrictive indexes for business rules.

The more complex the PK, the more space it takes up in the (hidden) index table that stores the contents of the index. You can see the actual table but the index table is not visible to us. Visible or not - according to ANSI SQL standards, it has to exist. So things that generate smaller index tables are more efficient in fitting into the buffers that hold the keys. If you have a non-unique index, you can't even rely 100% on the B-tree structure that Access uses internally to optimize an index search. It comes down to how many sardines can you fit in a tin can... and using autonumber PKs gives you the skinniest possible sardines.

 

[jaryszek]

Thank you very much The_Doc_Man!