SQL Join Table Naming Convention

SQL Join Table Naming Convention

It seems like the mapping table is storing all the roles that each user is a member of. If this is correct I would call the table UserRoles.

This correctly (IMO) pluralizes the intent of the table rather than UsersRoles which just sounds odd.

What should I name a table that maps two tables together?

There are only two hard things in
Computer Science: cache invalidation
and naming things
-- Phil Karlton

Coming up with a good name for a table that represents a many-to-many relationship makes the relationship easier to read and understand. Sometimes finding a great name is not trivial but usually it is worth to spend some time thinking about.

An example: Reader and Newspaper.

A Newspaper has many Readers and a Reader has many Newspapers

You could call the relationship NewspaperReader but a name like Subscription might convey better what the table is about.

The name Subscription also is more idiomatic in case you want to map the table to objects later on.

The convention for naming many-to-many tables is a concatenation of the names of both tables that are involved in the relation. ColourShape would be a sensible default in your case. That said, I think Nick D came up with two great suggestions: Style and Texture.

Naming convention for join tables, in a many to many relationship, in Laravel

The name of the pivot table should be account_group.

As given in laravel docs

To define this relationship, three database tables are needed: users,
roles, and role_user. The role_user table is derived from the
alphabetical order of the related model names, and contains the
user_id and role_id columns.

Though, you can always overwrite the default naming convention like below:

Account Model:

return $this->belongsToMany('App\Group', 'GroupsAccountAssignment', 'account_id', 'group_id');

Proper Table naming convention for a many-to-many intersect table

I usually use the names of both of the joining tables.

So, in your case, ClientBroker.

Table field naming convention and SQL statements

For queries that aren't ad-hoc, you should always prefix every field with either the table name or table alias, even if the field name isn't ambiguous. This prevents the query from breaking later if someone adds a new column to one of the tables that introduces ambiguity.

So that would make "id" and "name" unambiguous. But I still recommend naming the primary key with something more specific than "id". In your example, I would use student_id and teacher_id. This helps prevent mistakes in joins. You will need more specific names anyway when you run into tables with more than one unique key, or multi-part keys.

It's worth thinking these things through, but in the end consistency may be the more important factor. I can deal with tables built around id instead of student_id, but I'm currently working with an inconsistent schema that uses all of the following: id, sid, systemid and specific names like taskid. That's the worst of both worlds.

Database, Table and Column Naming Conventions?

I recommend checking out Microsoft's SQL Server sample databases:
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

What's the correct name for an association table (a many-to-many relationship)

Cross reference table. CustomerProductXRef.

SQL Table / Sub-Query Alias Conventions

I don't know of any canonical style rules for naming table/query aliases across databases, although I understand that Oracle recommends abbreviations of three to four characters.

I would generally steer clear of single letter abbreviations, except where the query is sufficiently simple that these should be completely unambiguous to anyone having to maintain the code - typically no more than two or three tables per query.

I would also generally avoid long alias names that conform to the general style of your database table-naming conventions, since it can become unclear what is a database table name and what is an alias.

In the example provided, the alias sT1 inside the inline view is utterly unnecessary, as there is only one table being accessed within that inline view. That leaves one table being joined to one inline view (based on the same table) in the query - in these circumstances, I would use s as the alias for the table, and s1 as the alias for the inline view (to indicate that it was querying the same underlying database table).

---

Related Topics