178

Let's say I have two tables:

Table: Color
Columns: Id, ColorName, ColorCode

Table: Shape
Columns: Id, ShapeName, VertexList

What should I call the table that maps color to shape?

Table: ???
Columns: ColorId, ShapeId
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
devuxer
  • 41,681
  • 47
  • 180
  • 292
  • ColorShape and ShapeColor alias for symmetry. – LukLed Nov 28 '09 at 18:53
  • 4
    I just came across a similar question I didn't see before: http://stackoverflow.com/questions/1764483/sql-join-table-naming-convention. Some other ideas from that thread: `Shape2Color`, `ShapeXColor`, `ShapeColorLink`. – devuxer Nov 28 '09 at 22:53
  • 3
    If there was a standard of naming junction tables - then this would not be opnion based. Then that would have been the answer to the question. By closing the question you shut down the possability for someone like myself to know wether there are standard ways of naming your junction tables or not. Please reconsider - the ones who closed it... – Lealo Aug 16 '17 at 16:25
  • 2
    I wrote a post blog about this issue of naming junction tables: https://world.hey.com/jdmo/how-to-name-your-junction-tables-3735fdc9 – mollerhoj Apr 01 '21 at 09:17

24 Answers24

232

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.

Community
  • 1
  • 1
tosh
  • 5,222
  • 2
  • 28
  • 34
  • 12
    +1 : well said - a carefully chosen name now will make maintainability a lot easier in the future. – Preet Sangha Nov 28 '09 at 21:09
  • 172
    "There are only two hard things in Computer Science: cache invalidation, naming things, and off-by-one errors" – Neil McGuigan Aug 09 '13 at 01:25
  • 2
    let's say, if there is a category for products and another category for recipe. for both tables we will have recipe_categories and product_categories, and since we can't just "categories" table name for both tables, in order to prevent the conflict, the junction tables for product and recipe will be "recipe_recipe_categories" and "product_product_categories" – c9s Jun 13 '17 at 07:47
  • 10
    Strictly logically speaking connecting information do not mean adding new information. I think that is what happens when you try to find a fitting word for junction tables. People reading newspapers do not make them subscribers, and there are not shapes without color. Using "subscribers" are adding new information (meaning) when connecting readers to newspapers. And there are not words to describe the connection of shape and color - there has never been something of the opposite, thus no name for it. Remember there are no new attributes given in a junction table. – Lealo Aug 16 '17 at 16:55
  • also don't use capital letters. reader_newspaper is better. capital letters cause grief in case-sensitive rdms like postgres – ChatGPT Dec 07 '20 at 12:47
46

How about ColorShapeMap or Style or Texture.

Nick Dandoulakis
  • 42,588
  • 16
  • 104
  • 136
  • 7
    +1 for coming up with something more elegant than just joining the names of the tables – tosh Nov 28 '09 at 21:01
27

Name the table whatever you like, as long as it is informative:

COLOR_SHAPE_XREF

From a model perspective, the table is called a join/corrollary/cross reference table. I've kept the habit of using _XREF at the end to make the relationship obvious.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
27

Interesting about half of the answers give a general term for any table that implements a many-to-many relationship, and the other half of the answers suggest a name for this specific table.

I called these tables intersections tables generally.

In terms of naming conventions, most people give a name that is an amalgam of the two tables in the many-to-many relationship. So in this case, "ColorShape" or "ShapeColor." But I find this looks artificial and awkward.

Joe Celko recommends in his book "SQL Programming Style" to name these tables in some natural language manner. For instance, if a Shape is colored by a Color, then name the table ColoredBy. Then you could have a diagram that more or less reads naturally like this:

Shape <-- ColoredBy --> Color

Conversely, you could say a Color colors a Shape:

Color <-- Colors --> Shape

But this looks like the middle table is the same thing as Color with a plural naming convention. Too confusing.

Probably most clear to use the ColoredBy naming convention. Interesting that using the passive voice makes the naming convention more clear.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • @Bill: All about the synonyms, and the synonym preference impacts the naming convention. – OMG Ponies Nov 28 '09 at 19:23
  • 2
    I suppose `HasColor` could be another possible name for the intersection table that uses natural language. – Bill Karwin Nov 28 '09 at 19:35
  • 1
    @Bill: The problem I have with that naming convention is `hasColour`/`ColouredBy` for what? It defeats the purpose of naming to have to use `DESCRIBE` to find out the relationships. – OMG Ponies Nov 28 '09 at 19:40
  • 5
    Similar to OMG Ponies's comment, what happens if other things can have a color? If I have another table that maps Text to Color, I would need a unique name. Maybe `ShapeHasColor` and `TextHasColor`? – devuxer Nov 28 '09 at 19:46
  • Yep, those are good comments. I think these are solvable within the guideline of using natural language as a naming convention. – Bill Karwin Nov 28 '09 at 20:17
9

Junction table

OR Bridge Table

OR Join Table

OR Map Table

OR Link Table

OR Cross-Reference Table

This comes into use when we go for many-to-many relationships where the keys from both the tables forms the composite primary key of the junction table.

priyanka.sarkar
  • 25,766
  • 43
  • 127
  • 173
8

A mapping table is what this is usually called.

ColorToShape
ColorToShapeMap
  • 1
    Actually, the term Mapping is generally used when the relationship is uni-directional (one to one or many) Is this the caswe? If so, then generally you don;t need another table. If color always determines shape, then add a shape column to the color table, or vice versa. An additional table is generally only needed when the relationship is many to many.. And in that case the term mapping is not appropriate. – Charles Bretana Nov 28 '09 at 18:45
  • 1
    In this case, any shape could be matched to any color, so it's many-to-many. – devuxer Nov 28 '09 at 18:48
  • 4
    BTW, whether it's a mapping table or not, I kind of like the idea of using `To` in the name. What if you have Shape with HighlightColor. If you call it `ShapeHighlightColor`, it's a little ambiguous whether it's a ShapeHighlight mapped to a color or a Shape mapped to a Highlight Color. So, `ShapeToHighlightColor` might be more clear. – devuxer Nov 28 '09 at 19:00
  • 2
    FYI: Oracle (9i/10g anyways) has a 32 character limit for table names, so you can't get *too* wordy. – OMG Ponies Nov 28 '09 at 19:04
  • 1
    I used the answer above but in a bit different way. i.e. Color_Shape_Map. Convention: Table1_Table2_Map – Goldfish May 02 '19 at 14:35
8

This is an Associative Entity and is quite often significant in its own right.

For example, a many to many relationship between TRAINS and TIMES gives rise to a TIMETABLE.

If there's no obvious new entity (such as timetable) then the convention is to run the two words together, giving COLOUR_SHAPE or similar.

Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
7

I've worked with DBAs that call it a join table.

Colour_Shape is fairly typical - unless the relationship has an explicit domain-specific name.

Dafydd Rees
  • 6,941
  • 3
  • 39
  • 48
  • 4
    I don't like underscores because they conflict with foreign key naming conventions such that you end up with nasty names like `Colour_Shape_Colour` and `Colour_Shape_Shape`. – Dan Bechard Apr 27 '16 at 20:43
7

I recommend using a combination of the names of entities and put them in the plural. Thus the name of the table will express connection "many-to-many".

In your case:

Color + Shape = ColorsShapes

Palindromer
  • 854
  • 1
  • 10
  • 29
  • 2
    If you are going the "names of tables" route, this is better since the singular version would "usually" be a namespaced table. – Andrew Haust Apr 06 '17 at 16:52
5

I usually hear that called a Junction Table. I name the table by what it joins, so in your case either ColorShape, or ShapeColor. I think it makes more sense for a Shape to have a color than for a Color to have a shape, so I would go with ShapeColor.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
4

Intermediate Table or a Join Table

I would name it "ColorShapes" or "ColorShape", depending on your preference

Neil N
  • 24,862
  • 16
  • 85
  • 145
4

I've also heard the term Associative table used.

a name for your table might be ColorShapeAssociations meaning that each row represents an association between that color and that shape. The existence of a row implies that the color comes in that shape, and that the shape comes in that color. All rows with a specific color would be the set of all shapes the color is associated with, and the rows for a specific shape would be the set of all colors that shape came in...

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • 1
    But what would you name the actual table? – devuxer Nov 28 '09 at 18:56
  • 2
    It depends on what the table is describing. If it says 'anything orange must be a rhombus', 'anything purple must be a circle', etc, then you might call it one thing (Colour_of_Shape, perhaps). If it is looser definition - known colors for shapes, allowing a single shape to appear many times - then perhaps 'Shape_Colour_Map'. – Jonathan Leffler Nov 28 '09 at 19:08
3

"Many-Many" table. I'd call it "ColourShape" or vice versa.

gbn
  • 422,506
  • 82
  • 585
  • 676
3

In general most databases have some sort of naming convention for indexes, primary key and so forth. In PostgreSQL the following naming has been suggested:

  • primary key: tablename_columnname_pkey
  • unique constraint: tablename_columnname_key
  • exclusive constraint: tablename_columnname_excl
  • index for other purposes: tablename_columnname_idx
  • foreign key: tablename_columnname_fkey
  • sequence: tablename_columnname_seq
  • triggers: tablename_actionname_after|before_trig

Your table is a linked table to me. To stay in line with the naming above I would choose the following:

  • linked table: tablename1_tablename2_lnk

In a list of table objects the linked table will be after tablename1. This might be visually more appealing. But you could also choose a name that describes the purpose of the link like others have suggested. This might help to keep the name of the id column short (if your link must have its own named id and is referenced in other tables).

  • or liked table: purposename_lnk
Holger Schmeken
  • 605
  • 6
  • 16
3

A convention I see a lot for joining tables that I personally like is 'Colour_v_Shape', which I've heard folk refer to colloquially as 'versus tables'.

It makes it very clear at a glance that the table represents a many-to-many relationship, and helps avoid that (albeit rare) confusing situation when you try to concatenate two words that might otherwise form a compound word, for example 'Butter' and 'Milk' may become 'ButterMilk', but what if you also needed to represent an entity called 'Buttermilk'?

Doing it this way, you'd have 'Butter_v_Milk' and 'Buttermilk' - no confusion.

Also, I like to think there's a Foo Fighters reference in the original question.

Breeno
  • 3,007
  • 2
  • 31
  • 30
1

I've always been partial to the term "Hamburger Table". Don't know why - it just sounds good.

Oh, and I would call the table ShapeColor or ColorShape depending on which is the more commonly used table.

rein
  • 32,967
  • 23
  • 82
  • 106
1

It's hard to answer something as arbitrary as this, but I tend to prefer tosh's idea of naming it after something in the actual domain instead of some generic description of the underlying relationships.

Quite often this sort of table will evolve into something richer for the domain model and will take on additional attributes above and beyond the linked foreign keys.

For example, what if you need to store a texture in addition to color? It might seem a bit funky to expand the SHAPE_COLOR table to hold its texture.

On the other hand, there's also something to be said for making a well-informed decision based on what requirements you have today and being prepared to refactor when additional requirements are introduced later.

All that said, I would call it SURFACE if I had insight that there would be additional surface-like properties introduced later. If not, I'd have no problems calling it SHAPE_COLOR or something of the sort and moving on to more pressing design problems.

Joe Holloway
  • 28,320
  • 15
  • 82
  • 92
1

Maybe just ColoredShape?

I'm not sure I get the question. Is this about this specific case or are you looking for general guidelines?

mafu
  • 31,798
  • 42
  • 154
  • 247
0

I would name it with the exact names of the tables being joined = ColorShape.

DOK
  • 32,337
  • 7
  • 60
  • 92
0

In adiction to what Developer Art has related, 

ColorShape

would be a usual naming convention. In ER diagram, it would be a relation.

j.a.estevan
  • 3,057
  • 18
  • 32
0

Call it a cross reference table.

XREF_COLOR_SHAPE
(
     XCS_ID INTEGER
     C_ID   INTEGER
     S_ID   INTEGER
)
EvilTeach
  • 28,120
  • 21
  • 85
  • 141
0

I'd use r_shape_colors or r_shape_color depending on its meaning.
r_ would a replacement for xref_ in this case.

Marius Burz
  • 4,555
  • 2
  • 18
  • 28
0

My vote is for a name that describes the table best. In this case it might be ShapeColor but in many cases a name different from a concatenation is better. I like readability and for me, that means no suffixes, no underscores and no prefixes.

magnus
  • 633
  • 6
  • 12
0

I would personally go for Colour_Shape, with the underscore: just because I have seen this convention turn up quite a bit. [but agree with the other posts here that there are probably more 'poetic' ways of doing this].

Bear in mind that the foreign keys should also be built on this join table which would reference both the Colour & Shape tables which would also help with identifying the relationship.

monojohnny
  • 5,894
  • 16
  • 59
  • 83