Convention for IDs of child tables

January 5, 2022

Every tutorial that I watched implemented child tables with two IDs, one ID for the table itself, and one ID that was just a reference to the parent’s table ID, like so:

CREATE TABLE Car (
  vin INT NOT NULL PRIMARY KEY,
  person_ssn INT NOT NULL,
  FOREIGN KEY (person_ssn) REFERENCES Person(ssn) -- Person's primary key
);

In my project, I’m doing a discord bot, and the only identifier that I need is the server’s ID. There’s no need for the child tables to be identified by something else than the server’s ID, like so:

CREATE TABLE Server (
  id VARCHAR(25) NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE configuration (
  server_id VARCHAR(25) NOT NULL PRIMARY KEY,
  FOREIGN KEY (server_id) REFERENCES Server(id),
  channel_to_message_id VARCHAR(25) NULL,
  master_role_id VARCHAR(25) NULL
);

So, am I wrong in making the primary key also the reference ID to the parent table? Is the "dual ID" method a convention?

>Solution :

If the configurations are a weak entity, which seems plausible, your implementation is right.

Weak entities occur relatively seldom, that’s why you may not have seen such in a tutorial yet.

But they exist and if the configurations are weak entities according to the logic of your model (only you can judge that in the end), everything is OK.