What Should I Name a Table That Maps Two Tables Together

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.

SQL naming convention for table that connects two tables?

See first reply from What should I name a table that maps two tables together?

Your question can be considered as duplicate, because already exist one with a good answer there.

And my personal answer for your question, I prefer to use a prefix for all many-to-many tables, example : MapGroupUser, where "Map" is the prefix. So, all tables of this particular type will be different from the others

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

Cross reference table. CustomerProductXRef.

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.

SQL mapping between multiple tables

To expand on Arthur Thomas's solution here's a union without the WHERE in the subselects so that you can create a universal view:

SELECT A.Name as Animal, B.Name as Zoo FROM A, AtoB, B
    WHERE AtoB.A_ID = A.ID && B.ID = AtoB.B_ID 
UNION
SELECT C.Name as Animal, B.Name as Zoo FROM C, CtoB, B
    WHERE CtoB.C_ID = C.ID && B.ID = CtoB.B_ID

Then, you can perform a query like:

SELECT Animal FROM zoo_animals WHERE Zoo="Seattle Zoo"

Mapping with two tables using Postgres

This can be solved with a database function. First, lets create the tables and fill them with example data.

Creating and filling table_1

CREATE TABLE table_1 (project_name TEXT, Date DATE, value TEXT);
INSERT INTO table_1(project_name, Date) VALUES ('P1','06/15/2016'), ('P2','04/25/2017'), ('P3','06/23/2017'), ('P4','05/12/2017') ;

Creating and filling table_2

CREATE TABLE table_2 (name TEXT, occ_june_2016 DECIMAL, occ_april_2017 DECIMAL, occ_may_2017 DECIMAL, occ_june_2017 DECIMAL);
INSERT INTO table_2(name, occ_june_2016, occ_april_2017, occ_may_2017, occ_june_2017) VALUES
  ('P1',          8.1,               7.5,           6.5,          8.2),
  ('P2',          8.3,               7.4,           6.0,          8.5),
  ('P3',          8.6,               7.1,           6.1,          8.1),
  ('P4',         8.8,               7.9,           6.8,          8.9);

Next, we create the function:

CREATE OR REPLACE FUNCTION getData(projectName TEXT, projectDate DATE)
  RETURNS DECIMAL
  AS
  $$
  DECLARE
    columnName TEXT :=  'Occ_' || trim(to_char(projectDate, 'Month')) || '_' || to_char(projectDate, 'yyyy');
    selectQuery TEXT := 'SELECT %s FROM table_2 where name = ''%s'' LIMIT 1';
    returnValue DECIMAL;
  BEGIN
  selectQuery = format(selectQuery, columnName, projectName);
  EXECUTE selectQuery INTO returnValue;
  RETURN returnValue;
  END;
  $$ LANGUAGE 'plpgsql';

The function takes 2 arguments. The first one (projectName) represents the name of the project (for example 'P1'). The second one (projectDate) is the date on which we want to retrieve the data (for example '15/06/2016').

We start by generating the name of the date column in table 2 from the the value of the projectDate variable. Once we have a name, we generate a dynamic sql query by replacing the %s placeholders with the values of the columnName and projectName variables respectively.

We execute the query and store the result in the returnValue variable which this function returns.

After we created the tables and the function, we can execute the following query:

 SELECT project_name, Date, getData(project_name, Date) FROM table_1;  

This query returns the following result:

project_name      date       getdata
P1             2016-06-15      8.1
P2             2017-04-25      7.4
P3             2017-06-23      8.1
P4             2017-05-12      6.8

Selecting a mapping table with fields from two other tables

If i understood correctly you are trying to get field1 from Table1 and field2 from table 2. If so you just need to join the three tables

SELECT a.field1, c.field2
FROM Table1 a
INNER JOIN Table3 b
ON a.id=b.Table1_id
INNER JOIN Table2 c
ON b.Table2_id = c.id

Table Naming Dilemma: Singular vs. Plural Names

Others have given pretty good answers as far as "standards" go, but I just wanted to add this... Is it possible that "User" (or "Users") is not actually a full description of the data held in the table? Not that you should get too crazy with table names and specificity, but perhaps something like "Widget_Users" (where "Widget" is the name of your application or website) would be more appropriate.

---

Related Topics