When should I give an SQL table its own numeric primary key?

Let's say I have a general content management system where I keep a table of nodes (navigation points that have associated permissions), and a table for each kind of node (blog post, comment, attachment, etc).

My node tables (in MySQL) look like so:

CREATE TABLE node_types (
    type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_parent INT,
    type_name VARCHAR(31) UNIQUE KEY,

    FOREIGN KEY (type_parent) REFERENCES node_types(type_id)
) ENGINE = InnoDB;

CREATE TABLE nodes (
    node_id INT PRIMARY KEY AUTO_INCREMENT,
    type_id INT,
    parent_id INT,

    FOREIGN KEY (type_id) REFERENCES node_types (type_id),
    FOREIGN KEY (parent_id) REFERENCES nodes (node_id)
) ENGINE = InnoDB;

Then to create different types of nodes I do something like:

CREATE TABLE attachments (
    node_id INT PRIMARY KEY,
    attachment_filename VARCHAR(255),
    attachment_title VARCHAR(255),

    FOREIGN KEY (node_id) REFERENCES nodes (node_id)
) ENGINE = InnoDB;
INSERT INTO node_types (type_name) VALUES ('attachment');

Using this method I can develop a generic permissions system that applies to nodes without having to specialize it for all my different node types by referencing the node_id.

In this situation, I didn't give attachments its "own" numeric primary key because an attachment is-a node with a 1:1 relationship to a node - its primary key is based on the node_id. But some people would / do. The attachments table could be easily rewritten as:

CREATE TABLE attachments (
    attachment_id INT PRIMARY KEY,
    node_id INT UNIQUE NOT NULL, -- a node is-a attachment.
    attachment_filename VARCHAR(255),
    attachment_title VARCHAR(255)

    FOREIGN KEY (node_id) REFERENCES nodes (node_id)
) ENGINE = InnoDB;
INSERT INTO node_types (type_name) VALUES ('attachment');

What do you think are the important reasons why or why not one wo开发者_StackOverflow中文版uld put give a numeric unique primary key ID to a table? In particular, I'm feeling that I'm sometimes inconsistent with the business of "not assigning a primary key to tables that have a 1:! is-a relationship".

If you have a child table (i.e. a table that has a foreign key pointing to the table) you most likely want to create a primary key on the parent table.

If you have a "leaf table" it's not really necessary. However, it may be a good practice to still have one. If you still want access to it because of a website and users creating bookmarks that contain a link to a record in a leaf table, you most likely want to add a primary key here, too.

In other words, it really depends.

Well if this will always be a 1:1 relationship your way should work.

There should not be any hard reasons to use a different uniqe id for the attachements.

I'd leave it as you currently have it. Introducing an extra numeric ID seems like a recipe for confusion - if you're looking for attachment 11375, is that its attachment_id or its node_id?

As general advice, I'd recommend that you have at most one surrogate key declared on any table, and as many real keys as are actually present. A surrogate key is a numeric key (e.g. int) that's used when none of the real keys are going to be suitable for inclusion in indexes, FK references, etc, such as being a varchar column or (depending on exact circumstances) keys which span multiple columns.