Is this lookup table (association) correct? (many to many)

Question

CREATE TABLE jokecategory (
  jokeid INT NOT NULL,
  categoryid INT NOT NULL,
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

especially the PRIMARY KEY (jokeid, categoryid)? or is there a better way to write this?

Thank you in advance, -)

Answer 1

Yes, this is a good table and primary key (I could call it an “associative” table, but I would not call it a “lookup” table).

Some people (not me) will insist that the jokecategoryid surrogate key column be the primary key; if you do this, you still need to restrict UNIQUE (jokeid, categoryid) to enforce the business rule.

Answer 2

, , .

CREATE TABLE jokecategory (
  jokeid INT NOT NULL REFERENCES joke (jokeid), 
  categoryid INT NOT NULL REFERENCES category (categoryid), 
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

Answer 3

Yes, this is a great table, as Tony has already pointed out. But I do not see the need to create it in the form of a regular heap table with a primary key index. These are two storage structures. It just creates an index structure, so my advice is to create this table as an organized indexed table.

Here is the link to the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/indexiot.htm#CNCPT911

Regards, Rob.