Suppose I have an artist table like:

id	name
1	John Coltrane
2	Springsteen

and a song table like:

id	title
1	Singing in the rain
2	Mimosa

Now an artist can write more than one song, and a song can be written by more than one artist. We have a many-to-many relation. We need an associative table!

How to design the primary key of the associative table?

One way would be to define a composite key of the two foreign keys, like this:

CREATE TABLE artist_song_map(
artist_id INTEGER,
song_id INTEGER,
PRIMARY KEY(artist_id, song_id),
FOREIGN KEY(artist_id) REFERENCES artist(id),
FOREIGN KEY(song_id) REFERENCES song(id)
)

Another way would be to have a synthetic primary key, and impose an unique constraint on the tuple of the two foreign keys:

CREATE TABLE artist_song_map(
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist_id INTEGER,
song_id INTEGER,
UNIQUE(artist_id, song_id),
FOREIGN KEY(artist_id) REFERENCES artist(id),
FOREIGN KEY(song_id) REFERENCES song(id)
)

Which design choice is better?

CodePudding user response：

Logically the both design is the same. But from administration aspect the identity design is more efficient. Less disk fragmentation and future redesign or maintenance will be easier.

CodePudding user response：

Unless you define the table as WITHOUT ROWID both queries will create the same table.

The column id in your 2nd way adds nothing but an alias for the column rowid that will be created in any of the 2 ways.

Since this is a bridge table, you only need to define the combination of the columns artist_id and song_id as UNIQUE.

If you want to extend your design with other tables, like a playlist table, you will have to decide how it will be linked to the existing tables:

• If there is no id column in artist_song_map then you will link playlist to song and artist, just like you did with artist_song_map.
  
• If there is an id column in artist_song_map then you can link playlist directly to that id.
  

I suggest that you base your decision not only on these 3 tables (song, artist and artist_song_map), but also on the tables that you plan to add.

CodePudding user response：

Bridge tables normally don't require a ID(auto_inCREMNT) to identify the rows.

The linking columns(foreign key) are the main point, as thea link artists to a 8or songs)

only when you need special attributes to that bridge or you want to reference a row of that bridge table and don't want to have ttwo linking columns, then you would use such an ID field, but as i said normally you never need it

CodePudding user response：

While, generally, the differences are minor, the composite/compound foreign key design sounds more natural. A separate primary key together with the associated index take additional space in the database. Further, if you use a composite primary key, you can declare the table as WITHOUT ROWID. According to the official docs, "in some cases, a WITHOUT ROWID table can use about half the amount of disk space and can operate nearly twice as fast".