(Triple) Compound Key (2 Viewers)

[dimbodoyle]

Hi First time poster!!


I am working on a project for my job and am creating a database to manage our inventory of parts. We have several depots around Europe and several hundred parts in each. We receive a new excel file containing all part movements across all hubs each day.

I am thinking of appending a master table each day with ;
Date,
depot_code,
Part_num,
qty

I was going to use, date, depot_code, part_num as a compound primary key. Does this go against any rules. i know 2 part compound keys are ok so I just wanted to check if triple keys are?

Thanks in advance.

 

[Dennisk]

you can use as many fields as you want to, although there will an overhead to update each if the indexes/

 

[The_Doc_Man]

Triple-field Primary Keys are legal. Unwise, but legal.

(The bigger the PK, the slower its performance.)

You might get greater functionality by having tables with NO PK but with non-unique indexes for date, part number, and depot. Then use QUERIES to sort by the individual elements you wanted to isolate.

Contrary to popular belief, not all tables require PKs. The one you describe does not (so far) seem to require one. If there are some non-obvious functions or factors you wanted to track, my opinion could change.

A properly normalized DB for this might include:

tblParts
PartID, PK, whatever your scheme is for numbering
Part data... whatever you keep for parts

tblDepots
DepotID, PK, whatever you use for Depot Codes
Depot data... whatever you keep for depots

{your table}
PartID - Foreign Key to parts table
DepotID - FK to depot table
ArrivedOn - date field showing arrival date.

Since this latter table is a JUNCTION table, it does not need a PK. You EXPECT a lot of entries that duplicate each other except for the arrival date.

 

[Banana]

I'm not 100% sure if that is correct but I believe a PK is needed if you want to allow duplicate entries. If the junction table has no PK, it means there can be only one unique combination of keys in the table. For example, table A has key 1, 2, 3 and table B has key 4, 5, and 6. In the junction table where both foreign key act as a composite key, we could have something like this:

1 & 4
1 & 5
1 & 6
2 & 4
2 & 5
3 & 6

and in that structure, if you tried to enter another "1 & 4", you'd get an error saying you're trying to create a duplicate record. In which case, you need a PK for the junction table...

(Mind you, this applies whether it's two, three, or even more than three ways junction)

Correct?

 

[The_Doc_Man]

tables with NO PK but with non-unique indexes for date, part number, and depot

- me

If there are some non-obvious functions or factors you wanted to track, my opinion could change.

- me

I believe a PK is needed if you want to allow duplicate entries

- Banana

Nope. A non-unique index is needed if you want to allow duplicate entries.

A PK in a junction serves no function UNLESS you are tracking something that depends on the junction entry as a grandchild of the two tables joined by the JUNCTION table. THEN you need a PK for THAT tracking.

As I explained before, it is a common misconception. If you wanted to search the junction table for the references to one or the other of its parents, you can have a non-unique index for that. The date combined with the other two indexes might or might not make it unique. But who cares? Normalization does not require PK on every table. It requires PKs on every table that is on the ONE side of a one/many relationship. The MANY side (which is, in this case, the junction table) has no such requirement.

Think of the junction table as a list of things. If your rules allow the same things to be used over and over again, your list will show that. If you put the date in the list, you can even sort by order of occurrence. But there is no cause to number the list permanently unless those numbers must be the target of some OTHER linkage. If not, skip the numbering.

 

[Banana]

Hmm. Please forgive my ignorance, but I honestly cannot fathom how any database engine would able to differeniate two otherwise identical junction record without such primary key.

Let us suppose we have a junction table for detailing a order with customers. Let's say that customer calls in morning, orders a widget. Order is processed as normal. Later, the customer realizes he needs two widget, so he calls back to make another order to get that extra needed widget.

Therefore, the junction table which tracks orderID and productID. We get thus:

OrderID  ProductID
1      5
1      5

How Access is to know which is which?

Furthermore, even using non-unique indexes, I'd imagine this would be a big performance hit as we are forcing Access to somehow resolve the duplicate entries back to their original records... correct?

 

[The_Doc_Man]

how any database engine would able to differeniate two otherwise identical junction record without such primary key.

The more direct question is whether you NEED to differentiate them. But don't forget that DATE was one of the elements mentioned.

I'd imagine this would be a big performance hit as we are forcing Access to somehow resolve the duplicate entries back to their original records

Banana, look at it this way. Inventory database has transactions saying "today I took out 20 left-handed doomiflangers" and on a different date, "today I took out 20 left-handed doomiflangers." So... if you need to know the difference, look at the date. If not, just sum the records to know that you have so far checked out 40 left-handed doomiflangers. But here is the question that governs whether EACH TRANSACTION needs a PK. Will there be a series of child records at a lower level of the hierarchy? Or are the transaction records the bottom of the food chain?

See, normally you need a PK on a table because you need to tag its children in a one/many relationship. But if there can BE no children, the tag is a waste of space because there will be nothing "below" the detail recordes to which a parental reference is required. Elementary transactions are, by their nature, exactly such a situation. Now, you MIGHT have differentiable parent records to these transactions - such as, say, an invoice number that leads back to two different invoices for two otherwise similar stock transactions. And at some point you will be able to say "because of the invoice numbers I can tell these transactions apart."

It comes back to this question: Will you need to differentiate between two records of a similar nature? In my experience, for the elementary level of DB transactions that was the subject of the original question, the answer is "I doubt it." (Not "Certainly you will not" - because there is always doubt.)

If you fear duplicate entries then you need to take steps to assure that the duplicate cannot be made. BUT setting an autonumber PK doesn't do that. You'll get a different autonumber and make the otherwise duplicate entry anyway. What I'm saying is that for autonumbered PK, you need to assure externally to the table that you won't duplicate the entry. And once an improper duplicate has been entered, you'll have one HELLUVA time seeing that it was really a duplicate and not the same customer making two similar orders on the same day. So knowing that the entry is or is not a duplicate once it's in the table isn't the issue. You prevent that by checking the invoice number and date, or other surrouding records that lead back to the transactions in question.

In the case you seem to worry about, why do I care which is which?

Oh, sure, you can imagine cases where you really need to distinguish between similar records. But my challenge is to do so for elementary transactions that have no child records. I feel I'm repeating myself so I'll stop here.

 

[Banana]

I had understood that autonumbering PK in a junction table cannot prevent duplicate entries; the earlier post had shown that earlier.

I just wanted to make sure if there was a case where a PK would be still needed for otherwise identical records, even if there were no children of the junction record. I should have had said it earlier, but I was thinking in cases of where I might query the junction table to give me a list of what was ordered.

But I think you've answered that as well; if for whatever reason, I needed to know each individual record, then I'll need a PK to differeniate between otherwise two identical records. But if I'm more interested in getting a count/sum/average of the junction tables, then big whoops, who needs a PK?

Thanks for clarifying!

 

[The_Doc_Man]

No problem.

For future reference, what I stated is a pretty general rule. You need a PK only if you can have children.

Or, in street parlance, we only need an ID if some records will ever ask Access, "Who's my daddy?"

 

[dimbodoyle]

Wow Thanks Guys
I didnt expect this much thought to be put into the answer!
Much appreciated!

 

[The_Doc_Man]

No problem. We're here because we like to help.

 

[Pat Hartman]

Unlike Doc, I ALWAYS use primary keys. Usually my primary keys are autonumbers since this makes joins easier and prevents issues with user fields that need to change values. However, if there is a user field or set of fields that the business rules say must be unique, I use a unique index. This eliminates the issue with multi-field joins but satisfies the business requirement. On the subject of junction tables, I NEVER use autonumber PKs UNLESS, the junction table has children. So a junction table would by my definition almost always use a compound PK with at least 2 fields and occassionally more.

Problems caused by having no PK:
Access will not allow updates in certain cases when a table with no PK is joined to other tables.
Acces will not allow deletes of duplicate rows when the table has no PK.
If the table is ODBC, even a single table is not updatable if it has no PK.

There may be others.

 

[The_Doc_Man]

Pat?

I've successfully performed DELETE * FROM TABLEX WHERE [FIELDY]="Z"; (and the table had no PK and some duplicates existed). Now I could understand not allowing deletion of ONE of the duplicates, but I wonder about your second problem statement. Perhaps it is too general?

 

[Pat Hartman]

My statement was probably not precise enough and now that I think about it, the issue I ran into might have been with an SQL Server table. If the entire row is duplicated, Jet cannot uniquely identify which one you want to delete and so it has no way of communicating that to SQL Server. You may be able to delete all the duplicates but not just one of them. You can delete non-duplicated rows without problem.

The way I "delete" duplicates in this situation is by creating a new table with the proper PK defined. I then append the old table and ignore the error at the end that says "x rows were not added due to duplicate keys".

In any event, it doesn't hurt to define an autonumber PK if you have nothing better and it does remove the pain of some other situations so I don't even think about it. I just do it.

 

[The_Doc_Man]

I understand. I agree that if you have a desire to eliminate one but not both of the duplicate rows, you need a way to specify which one is gonna get fried. In the original context of the discussion, and from other threads with Banana, I had gathered that in some cases, there was no need to have a PK. You are placing a PK by preference and I surely agree that preferences count. Nor is it a bad habit. But then, I'm a N'Awlins boy who learned a LOT of bad habits while playin' music in Bourbon Street bars. Guess I've allowed OTHER bad habits to creep in, too.

 

[Banana]

A bit of tangent, I have to say I find it odd that you can't specify TOP value for a DELETE query, which would made deleting one of duplicate far more easier than the present solution of using a subquery to return only one value to be deleted. But with INSERT INTO (or was that UPDATE?), you can set an TOP value... Go figure.

 

[Rabbie]

TOP 1 can return more than 1 record in Access if the sort criteria are the same so if you just want one then you need to find a way to discriminate. THis is not the case with all SQL database systems

 

[Banana]

Really?

Mind posting an example SQL?

I know I've ran append queries using TOP value and added exactly one record when it otherwise would have had added more....

 

[Rabbie]

Quote from this link http://www.allenbrowne.com/subquery-02.html

If the subquery might find multiple matches, the main query has just one cell to display the results, so you get this error.

This one can be hard to test: you don't get the error until particular combinations of data result in multiple matches, so the query may work for months, and then suddenly fail. It is therefore crucial to design the subqueries to avoid this issue.

Solutions
Use the TOP predicate in the subquery, e.g.
(SELECT TOP 1 MyID FROM ... )

TOP 1 is not enough! If two records tie, Access cannot decide between them. Unlike some other databases, it returns both. The trick is to give JET a way to choose between equals. To do that, include the primary key in the ORDER BY clause like this:
(SELECT TOP 1 OrderDate FROM Orders ORDER BY OrderDate DESC, OrderID)
Since the primary key value is unique, Access can decide which is the TOP 1, even if you have multiple orders on the same date.

Another alternative is to use aggregation, e.g.
(SELECT Max(OrderDate) AS MostRecentDate FROM ... )
This is somewhat less flexible, but preferred where it works.

I find this site very useful for a lot of my Access problems

 

[Banana]

Very interesting! I wonder why Access would behave differently from other SQL engines. Nonetheless to know about the potential pitfalls.

Yes, I consult his website frequently; very good information there.