In pure relational database theory, there is nothing to stop you having composite primary keys (PKs), and you can have foreign keys (FKs) that reference them and those FKs are necessarily composite too. Some software has difficulty with composite keys, so you often find that people add an ID column which contains an automatically generated number, which is then designated as the PK of the table. Other tables can then have (simple) FKs that reference the (simple) ID column. One not uncommon mistake is to forget that the original composite PK is still a candidate key (CK), and its uniqueness should be enforced by the DBMS with a unique constraint on the table; it becomes an alternative key (AK).
The system of CKs, AKs and PKs works like this:
- Every CK is a set of (one or more) columns that is a unique identifier for the data in the rest of each row of data in a table.
- One CK may be designated as the PK.
- The other CKs become AKs.
Consider this table:
CREATE TABLE elements
atomic_number INTEGER NOT NULL PRIMARY KEY
CHECK (atomic_number > 0 AND atomic_number < 120),
symbol CHAR(3) NOT NULL UNIQUE,
name CHAR(20) NOT NULL UNIQUE,
atomic_weight DECIMAL(8,4) NOT NULL,
period SMALLINT NOT NULL
CHECK (period BETWEEN 1 AND 7),
group CHAR(2) NOT NULL
-- 'L' for Lanthanoids, 'A' for Actinoids
CHECK (group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
'7', '8', '9', '10', '11', '12', '13',
'14', '15', '16', '17', '18')),
stable CHAR(1) DEFAULT 'Y' NOT NULL
CHECK (stable IN ('Y', 'N'))
name is a candidate key. For chemistry, the
symbol is most convenient as the primary key; for physics, the
atomic_number is most convenient. The tables related to isotopes etc reference the
atomic_number column, but the tables related to chemical compounds reference the
symbol column. The three CKs here are all simple; on the other hand, the isotopes table has a compound PK consisting of the atomic number of the element (the number of protons) and the number of neutrons.
Getting back to your question, your data may well be in 3NF, or more likely BCNF (which is formally stronger than 3NF).
You'd have to show us your table schemas and specify the constraints (functional dependencies, etc) that apply to the columns before we could assess your design. But there is nothing that you've described which, a priori, prevents it from being well normalized.