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: 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

Convention for IDs of child tables

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
);

​x
 
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
);

 
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?

Answer

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.

Advertisement

Answer