code:In this case, the "id" would become the primary key. The additional "id" column isn't necessary, you could make the name the primary key, but keeping the primary key separate from the actual data in the table is good practice. This is called using a "surrogate key". To see the pitfalls of using name (the "natural key"), imagine the likely scenario that the name of a court changes. When doing this, you'd have to update any links to this table using the name.create table court(
id integer,
name varchar(50)
)
code:Again we use a surrogate key. If we didn't, you'd probably need to make some combination of the other columns a composite primary key (possible all of the other columns), and that gets rather messy.create table mention(
id integer,
book varchar (50),
title varchar (50),
chapter varchar (50),
section varchar (50),
article varchar (50)
)
code:Rows in this table simply link the other two tables together in a meaningful way.create table court_mention(
court_id integer,
mention_id integer
)