Is a composite key correct here?

Question

I have two tables, Lpn and Uld that will relate on one column, that is, UldNo.

To give some background on the issue

An Lpn is a label that is assigned to one or more pieces of freight in a warehouse. A Uld is a pallet that the freight is built on so that it can go on an airplane. Every Uld has a unique identifier on it, consisting of a 3 letter type, 5 digit serial number, and 2 letter suffix that indicates the owner of the pallet. A Uld can come through a warehouse multiple times, and each time would have different Lpns (freight) on it.

To ask about the database

An Lpn row will only relate to one Uld row, and a Uld row may relate to multiple Lpn rows. The issue comes along is that UldNo cannot be the sole PRIMARY KEY (PK) for Uld because a Uld may be reused later (with different Lpns), so I created a PK of ID. If I try to create the relationship, I get an error because UldNo is not a PK. From everything I've learned, composite keys should be avoided. Is it correct to use a composite key in this situation? And if so, do I need to create the ID column in the Lpn table to create the relationship?

The following assumptions are correct:

1. On each flight, multiple pallets are transported.
2. Each pallet contains multiple LPNs (labels)
3. Pallets get reused.
4. LPNs (labels) are NOT reused.

The DBMS I'm using is SQL Server 2016 Express.

Answer 1

Maybe the following approach would be of help (the parent tables Uld and Flight should be created first; test server running PostgreSQL 9.3)

create table Uld ( uldno varchar(10) primary key);

create table flight (flightid varchar(32) primary key);

create table LPN (
  id integer primary key
, uldno varchar(10) references Uld(uldno)
, flight varchar(32) references Flight(flightid)
, unique (uldno, flight)
);

Test data:

insert into Flight values ('A'),('B'),('C'),('D');

insert into Uld values ('AAA12345ef'),('BBB12345gh')
, ('CCC12345ij'),('DDD12345kl');

insert into LPN values 
(1, 'AAA12345ef' ,'A'),
(2, 'AAA12345ef' ,'B'),
(3, 'AAA12345ef' ,'C'),
(4, 'BBB12345gh' ,'A'),
(5, 'BBB12345gh' ,'D'),
(6, 'BBB12345gh' ,'C'),
(7, 'CCC12345ij' ,'A'),
(8, 'CCC12345ij' ,'B'),
(9, 'CCC12345ij' ,'C'),
(10, 'DDD12345kl' ,'A');

We get (something like):

+------------+
| table: Uld |
+------------+
| uldno [PK] |
+------------+
|AAA12345ef  |
|BBB12345gh  |
|CCC12345ij  |
|DDD12345kl  |
+------------+

+-------------------------------+
| table: LPN                    | 
+-----------------+-------------+
| id | uldno [FK] | flight [FK] |
+-----------------+-------------+ 
|1   | AAA12345ef |A            |
|2   | AAA12345ef |B            |
|3   | AAA12345ef |C            |
|4   | BBB12345gh |A            |
|5   | BBB12345gh |D            |
|6   | BBB12345gh |C            |
|7   | CCC12345ij |A            |
|8   | CCC12345ij |B            |
|9   | CCC12345ij |C            |
|10  | DDD12345kl |A            |
+-----------------+-------------+

+---------------+
| table: Flight |
+---------------+
| flightid [PK] |
+---------------+
|A              |
|B              |
|C              |
|D              |
+---------------+

Testing:

-- eg which Ulds are on flight A?
select uldno
from LPN 
where flight = 'A';

The data types for LPN.id and Flight.flightid should be adjusted as per requirements. This layout should also work if there are several LPNs per uldno (if they have the same flightid in the LPN table). It may aid your understanding of "keys" if you (also) regard them as "constraints" (~ rules that define certain limitations see eg https://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322).

Answer 2

Quick answer: You would need to tie the Lpn and Uld tables together based on Uld.ID, since that's Uld's primary key.

Composite logical keys are unavoidable, and can make sense as your actual keys. There are some potential drawbacks:

• Assuming the primary key is also the clustered index, a wide key affects any other indexes on the table (because the clustered key is the pointer to the record for other indexes). Thus, an integer value as a primary key would consume much less space than a composite key consisting of a CHAR(11) and a datetime field.
• If any of the values in the logical key are user-facing values, they may be subject to change due to business request (probably not the case here, but worth noting). This change requires updating not just the source table, but all indexes on that table, and all tables where the value is part of a foreign key.
• And, I'm sure, there are more....

If these would resent problems, you can use a surrogate key (often a simple auto-incrementing integer value). NOTE: In an OLTP system, where hundreds of new records may need to be created every second, this set-up can cause performance issues (because every process is trying to write to the same place in the table); there, going back to a composite key (or using a non-sequential primary key, like a GUID) makes more sense.

Some additional notes on your personal situation:

Does Uld include some data that is the same every time the pallet shows up (for instance, the pallet owner), and other data that is unique to each appearance (for instance, the date of arrival)?

If so, you may want one table of the pallets themselves, with all the information that's static and unchanging, each time they show up, and a second table that reflects the information that's unique to each appearance.

If that's not the case, then Uld should still have some value that differentiates between when it was here last month, and its presence today. I'll guess that's a date/time stamp of some sort. In that case, the logical primary key of the table would be the UldNo and that date. (If it does make sense to have a Uld_Master table where the primary key would be the UldNo, and a separate Uld_Usage table tying back to Uld_Master on UldNo, then Uld_Usage would again wind up with UldNo plus the date field (or whatever) as its logical key).

As noted above, it may make sense to create a surrogate key for the table representing the usage of the pallet at a specific point in time, and linking that table to Lpn using that surrogate key. In fact, this is what you've already done (except for the linking part).

Answer 3

I agree with others that it is logically acceptable for a table "primary key" to be composite, in separate table columns. But I wouldn't treat your case that way. There's probably some philosophical description of the ideal true meaning of "primary key" as opposed to some other property that happens to be unique or is made unique. For instance, you could use an arbitrary integer as a table row identifier and a clustered index, although the "primary key" is, say, 100 bytes. Perhaps a table doesn't have a primary key at all.

I'm confused about your pallets. I think of a pallet as a wooden table with no legs, on which freight is carried. Let's say I own a pallet named BIG00001RC as its UldNo, and I use it to ship 1000000 copies of Donald Trump Souvenir Calendar Of Year 2016 from the printing press in Mexico City. One year later, pallet BIG00001RC is now carrying my shipment of Donald Trump Souvenir Calendar Of Year 2017. Now, does that require a second row in table Uld, for the same wooden pallet, used for a separate task? And so, yes, definitely UldNo is not suitable as the key of table Uld. Instead, I would use your "ID" column as the entire key of Uld.

In this case, UldNo would not necessarily be stored in the Lpn table at all; only ID is duplicated in Lpn. If you want to know - for instance - which pallets have packets of lithium-type batteries on board (these batteries are an air safety problem), you'd write a query that selects those Lpn freight items, uses ID to find corresponding rows in Uld, and then reads the pallet UldNo held in Uld.

SELECT Uld.UldNo FROM Uld JOIN Lpn ON Uld.ID = Lpn.ID
WHERE CHARINDEX('Lithium', Lpn.Description) > 0 AND Uld.DeliveryCompleted = 0 -- boolean!

Of course UldNo should be unique in the set of rows with DeliveryCompleted = 0. And maybe you can make that a constraint, although that reminds me that I've had poorer performance using a boolean column value in query logic: it actually worked better when I used a whole byte column (type tinyint) to store 0 or 1. But this may have been improved since I tried it.

Incidentally I suppose that I may have changed my company name from "Mexico Calendar Company" to "Trump for President Calendar Company" in the meantime, and I haven't decided yet about next year. If I get a new row in table Uld each time I send the same pallet on another journey, the different company name isn't a problem, or, it isn't so much a problem.

It isn't forbidden by this design to have UldNo as another column in Lpn and to write a simpler query to get it out - in real life your query may involve twenty tables, and using one fewer is a relief - but the actual key relationship between tables relies on ID, and Lpn.UldNo is now an unnecessary duplicate copy of that data.

Actually, about composite keys, I believe I've had depressed performance with them, but that is specifically in a design of tables where in some places the key is columns a, b, c - 2, 2, and 3 characters - and elsewhere the same identity is represented in column abc, 7 characters. I'm writing conditions like "x.a+x.b+x.c = y.abc", and this seems to not run well; no error, but it isn't efficient. Worse - or it feels worse - is to write "x.a = LEFT(y.abc, 2)". I think I've benefitted instead by altering a table design to contain columns a, b, c, and abc all together - or to add column a where column abc already exists, so that I can refer to whichever one is appropriate in a task.

In your case, it's different, if using UldNo, one column containing composite information, as a key is eliminated. In that case, you may have an actual use for separate columns of UldType, UldNumber, UldOwner, with UldNo never used inside the database but only output. I expect that the cost of adding char columns together in the query result output is meaninglessly small. On the other hand, it may be useful to you, as it is to me, to hold columns of the sub-strings of UldNo and also the whole - obviously, to compare one set of pallets to another (pallets which should be in the airplane, and pallets which are in the airplane when someone counted them).

Also, is there a BIG00001 and a WEE00001, or is the number itself unique... and, if unique, are numbers often misread, so that the other data becomes useful confirmation? I'm currently waiting for my customer to confirm that an order for item 7762200 was meant to be for item 7762020 - we don't have a 7762200. (These aren't the real numbers, but this is how they have been confused - I'm fairly sure.)