sqlpostgresqlindexingdatabase-designconstraints

Removing a table without a unique index / constraint


CREATE TABLE "lemmings" 
(
    "id" bigserial,
    "name" varchar(255) NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE "speeches" 
(
    "id" bigserial,
    "title" varchar(255) NOT NULL,
    "year" integer NOT NULL,
    PRIMARY KEY ("id")
);

No other tables exist in this schema.

The lemmings table has no unique indexes / constraints. This mean two lemmings can have the same name.

A lemming can give multiple speeches, and a speech can be given by multiple lemmings.

To clarify: a lemming can give more than one speech, and a speech can be given by more than one lemming.

Given the above, I think that the lemmings table is redundant, and the following steps should be taken:

Is my reasoning correct?


Solution

  • As you can’t (shouldn’t) have multiple values (lemming names) in a single column, you have model this with an intersection table? The fact that you only have a name attribute for lemmings doesn’t impact how you model M:M relationships