Search code examples
sqldatabasesqlitenamingcreate-table

SQL junction table naming: making it readable vs. following convention

Consider the following "user" table:

CREATE TABLE user (
    user_id INTEGER PRIMARY KEY NOT NULL,
    nick_name TEXT NOT NULL UNIQUE
)

Now I have a many-to-many reference table which connects 2 users in a "following" relationship (which is not a two-way relationship btw).

I've read a lot about naming stuff in sql but most of the times it's really inconsistent (some say PascalCase, some snake_case eg.).

My question is, which of the following naming is better?

readablity > convention:

CREATE TABLE user_following_user (
    follower_user_id INTEGER NOT NULL,
    target_user_id INTEGER NOT NULL,
    FOREIGN KEY ...
    PRIMARY KEY ...
)

convention > readability:

CREATE TABLE user_user_xref (
    user1_id INTEGER NOT NULL,
    user2_id INTEGER NOT NULL,
    FOREIGN KEY ...
    PRIMARY KEY ...
)

Keep in mind that there may be other tables that also have 2 users in relation (say a blocking table), which is why I'm not sure what's the correct way to name (junction-) tables.

Maybe there's another way to follow conventions while maintaining readability. I appreciate every tip.

Solution

  • Go for readability every time. Be kind to the person maintaining your sql code; it may be you in 6 months from now. Also, check where the "conventions" you are following come from. What reason would anyone have for defining a convention that eliminates any explanation of what you are doing (like the second example)?
    You may find this helpful: the names we give things.