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.....

 

[Pat Hartman]

Having been creating relational databases since the early 80's, I came from the natural key school but I don't like multi-field primary keys so I use autonumbers almost exclusively and create unique indexes to enforce business rules. It simplifies the joins and protects you from changes to your natural keys. You will find that particularly with Access, multi-field PKs cause a problem. For example, if you use a combo or listbox, you need a SINGLE unique identifier to make them work correctly. That will almost always be an autonumber.

FYI, Relationships are always ONLY on the PK to a FK. Never, index to index. Whenever you have an autonumber in your table, it should be the PK or there is no reason for it to be there.

 

[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

 

[Pat Hartman]

Your graphic is incorrect. To relate t1 to t2, remove the "index" fields from t2 and replace them with the PK from t1. So
table1
PK1 (autonumber PK)
fld1 (unique index f1)
fld2 (unique index f2)
fld3

table2
PK2 (autonumber PK)
PK1 (long integer, FK to table1.PK1)
fldA
fldB

PS, naming all the pk's PK or ID just causes confusion. Give each field a meaningful name like CustID, StudentID, GradeID, etc. Use the same name for the foreign key whenever possible. That makes it easy to see which field is the FK and which table it points to.

 

[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

 

[Pat Hartman]

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

It suggests a compound index on fld1 and fld2. That provides uniqueness. I should have made real column names and spelled out the compound index better. I was referencing f1 as the first field in the index and f2 as the second field in the index, NOT their field names.

 

[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!