Should I be using a composite key for this table?

Question

My database is going to store words, so the Word table is the most important. The Rhyme table lists which words a particular word perfectly rhymes with. It consists of three fields:

RhymeID - primary key.
WordID - foreign key linking to Word table
Rhyme - a non key integer which refers to a different record in the Word table.

E.g. 'cat' has WordID 009, 'bat' has WordID 014 and 'fat' has WordID 035, two records in the Rhyme table might look like this

RhymeId - WordId - Rhyme
001 - 009 (cat) - 014 (bat)
002 - 009 (cat) - 035 (fat)

The question is, should I make WordId a primary key to form a composite key with RhymeId? Do I need it to uniquely identify each record? I have no practical experience with databases, this one is still in the planning stage.

Answer 1

First make it clear than Rhyme is also a FK

You should do either of the following:

• Make RhymeId the PK and create an composite unique index with WordID and Rhyme so no combination of two words appears twice.
• Get rid of RhymeId and make a composite PK with WordID and Rhyme

I preffer the second option because you need to keep only one index instead of two.

Answer 2

Two concepts from the DB design world apply here. They are "natural keys" and "surrogate keys". A natural key is that combination of columns, the values of which can be used to distinguish the row which contains them from all other rows in the table i.e. that combination of values is unique in the table. Importantly these columns appear "naturally" in the user's understanding of the problem. A surrogate key is a value invented to stand in for the natural key, usually for technical or performance reasons. Once you create a surrogate key it is normal practice to use that as the foreign key in all other tables.

In your example "cat" wold be the natural key of the Word table and "009" is the surrogate key for that natural key.

So, for your Rhyme table the natural key would be WordID and Rhyme (yes, even though they are both themselves surrogate keys from another table) and RhymeID is the surrogate key.

If other tables will have a foreign key pointing back to Rhyme then I would definietly suggest giving it a surrogate key. If it won't then the surrogate is 50% bloat for zero value in what is likely to be an exceedingly large table; I would suggest omitting it.