Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
When creating a database structure, I tend to create unique composite keys for every group of data that needs to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.
On one hand, the benefit is that I can avoid the insert of 'bad records'. Are there cases where the usage of these keys becomes too much or is sign of bad db design?
If the intention is to build a relational database, then the objective should be to reflect the informational characteristics of the relevant business environment in the database layout, so the short answer is:
A database designer must define one or more composite KEYs whenever each row of the table under consideration has to be uniquely differentiated by the values of one or more combinations of columns.
In this way, a database designer is responsible for (1) the identification of that business context requirement and for (2) the declaration of the corresponding multi-column KEY constraint(s).
As you know, it is his or her duty to (i) identify and (ii) declare single-column KEYs too, whenever necessary.
Therefore, the general procedure should be to always analyze very carefully each particular situation, inspecting the meaning ascribed to every individual table and column, taking into account all their interconnections regarding the entire database layout.
By analyzing very carefully each particular situation, one can as well determine the quite specific cases when, apart from the declaration of one or more single- or multi-column natural KEYs (as demanded), a table may benefit from the inclusion of an extra column to retain system-controlled surrogatesa (surrogates for brevity, which are implemented, e.g., via an IDENTITY property in Microsoft SQL Server or an AUTO_INCREMENT attribute in MySQL), as such an artefact is at all times an attached element that entails supplementary administration tasks (e.g., it regularly needs a accompanying INDEX at the physical level, with all its implications).
The point brought up above is pertinent because, according to my interpretation of the following question excerpt “I usually use a primary key (usually INT AI id)”, what you are describing is, precisely, a column that retains surrogates.
In this respect, since optimizing the usage of the database platform resources (the database management system, the hardware, etc.) is paramount, it must be mentioned that a database practitioner should employ any kind of technical additions only when it is justified by the features of the business context represented in the database of significance.
A column holding surrogates that somewhat work as “substitutes” for the respective natural KEY values can sometimes be of help when, e.g., it is utilized in tables that:
In this manner, FK references are made to a column containing values that are physically lighter in terms of bytes (when compared with references made to a relatively large combination of columns and/or references made to columns set up with heavy types and sizes) which has impacts on, e.g., disk space and memory consumption due to the reduction of the physical-level scaffoldings that support the concerning logical-level definitions.
Data manipulation
In cases of tables that stand for (conceptual) entity types that belong in the lower layers of the data hierarchy of the business context of interest, composite PRIMARY KEYs (PKs) tend to be more effective since they commonly are called for in (logical) SELECT operations that include columns that point to PKs of tables at higher layers of the aforementioned hierarchy, while columns with surrogate values are typically not involved.
Along these lines, the use of composite PKs is generally convenient to avoid needless JOINs (which does not imply in any way that the JOIN operator is a bad tool, quite the opposite, it is an essential, elegant and powerful instrument with regard to data derivation that functions very fast in the right settings), and in agreement with the optimization of platform resources previously brought up, one should employ and supply mechanisms for data manipulation that are as efficient as possible.
For instance, with a hypothetical logical-level SQL-DDL layout like the one that follows:
--
-- Let us suposse that we are building a database in which:
-- a. A row of Foo may be associated with zero-one-or-many rows of Bar.
-- b. A row of Bar can be associated with zero-one-or-many rows of Baz.
-- c. A row of Foo is identified by the values of its own columns
-- (Qux, Corge, Grault, Garply, Etcetera) alone.
-- d. A row of Bar is identified by its BarDateTime value of creation along with
-- the FooId value of the Foo row with which it is associated.
-- e. A row of Baz is identified by its BazNumber value along with
-- the FooId and BarDateTime values of the Bar row with which it is associated.
-- 1. The representation of the ‘top layer’
-- of the hypothetical conceptual data hierarchy:
CREATE TABLE Foo (
FooId INT NOT NULL, -- Column for surrogates.
Qux CHAR(60) NOT NULL,
Corge CHAR(80) NOT NULL,
Grault VARCHAR(90) NOT NULL,
Garply CHAR(75) NOT NULL,
Etcetera CHAR(48) NOT NULL,
CreatedDate DATE NOT NULL,
--
CONSTRAINT Foo_PK PRIMARY KEY (FooId),
CONSTRAINT Foo_AK UNIQUE (Qux, Corge, Grault, Garply, Etcetera) -- ‘Voluminous’ composite ALTERNATE KEY.
);
-- 2. Conveying the ‘middle layer’ of the
-- conceptual data hierarchy:
CREATE TABLE Bar (
FooId INT NOT NULL,
BarDateTime DATETIME NOT NULL,
Waldo CHAR(30) NOT NULL,
Etcetera CHAR(62) NOT NULL,
--
CONSTRAINT Bar_PK PRIMARY KEY (FooId, BarDateTime), -- Composite PK.
CONSTRAINT Bar_to_Foo_FK FOREIGN KEY (FooId)
REFERENCES Foo (FooId)
);
-- 3. Portraying the ‘bottom layer’
-- of the conceptual data hierarchy.
CREATE TABLE Baz (
FooId INT NOT NULL, -- This component of the PK provides ease of ‘navigation’.
BarDateTime DATETIME NOT NULL, -- This component of the PK also provides ease of ‘navigation’.
BazNumber INT NOT NULL,
Fred CHAR(28) NOT NULL,
Etcetera CHAR(57) NOT NULL,
CreatedDate DATE NOT NULL,
--
CONSTRAINT Baz_PK PRIMARY KEY (FooId, BarDateTime, BazNumber), -- Meaningful composite PK.
CONSTRAINT Baz_to_Bar_FK FOREIGN KEY (FooId, BarDateTime) -- Composite FK.
REFERENCES Bar (FooId, BarDateTime)
);
--
…it is possible to JOIN (a) columns FROM table Foo with (b) columns FROM table Baz without having to “pass” first “through” (c) table Bar, making use of Foo.FooId and Baz.FooId as the only necessary conditions. To this extent, when one can save one or more “manoeuvres” in a certain data manipulation operation, more platform resources will be available for other concurrent processes.
In this series of posts you can see a more “concrete” example of a conceptual schema and its respective logical representation where the configuration of composite PKs offers multiple advantages.
Tendencies
Nevertheless, these are tendencies, hence, as noted before, you should always inspect every exact case (considering its effects on the overall scenario) to define whether or not appending a column for surrogates is beneficial.
Data constraints
On the other hand, there is a quite relevant detail that you refer to in the paragraph cited below:
When creating a database structure, I tend to create unique composite keys for every group of data that need to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.
It is really important to take that detail into account because a surrogate value does not carry business context meaning, thus it does not uniquely identify the real data elements of a row, and that aspect is exclusively guaranteed, at all times, by meaningful natural (be it single- or multi-column, either PRIMARY or ALTERNATE) KEY values.
Apart from that, it is as well opportune to remember that composite KEYs, working in conjunction with FKs, are of great value to establish declarative restrictions that ensure that the pertinent rows comply with the cardinality ratios of the conceptual relationships/associations that exist between the entity types represented by the tables of a database.
As you are well aware, the task of constraining the data properly is of enormous significance in the field of database administration, for it ensures that the information managed is consistent with its conceptualization in the real world (i.e., the applicable business environment). In this manner, having consistent data, the involved manipulation operations yield reliable results, and the end users of a database (and/or one or more application programms sharing access to it) can use it as a valuable instrument to make well-informed decisions.
Some of these topics and other logical, physical and practical factors are discussed in my answer to the question entitled
so you might find those posts of help.
As for terminology concerns, you may also find of interest my deliberations contained in the meta post entitled
Endnote
a System-controlled surrogates (i.e., those generated and assigned by the database management system) were assessed in the 1979 paper named Extending the Database Relational Model to Capture More Meaning by Dr. Edgar Frank Codd, the creator of the relational paradigm. In such paper, it is prescribed that, among other points, system-controlled surrogate values must never be displayed to database users, although in most databases I have observed, that rule is not complied with (perhaps it should be enforced by the database management system itself, automatically).
This answer is based on the supposition that, in the scenario at hand, system-controlled surrogates are shown just like normal business context values. It is worth to point out that system-controlled surrogates are not part of the original relational model, as published in the 1970 paper entitled A Relational Model of Data for Large Shared Data Banks, also written by Dr. Codd, of course.
