Take the 2-minute tour ×

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
share|improve this question
ColorShape and ShapeColor alias for symmetry. –  LukLed Nov 28 '09 at 18:53
I just came across a similar question I didn't see before: stackoverflow.com/questions/1764483/…. Some other ideas from that thread: Shape2Color, ShapeXColor, ShapeColorLink. –  devuxer Nov 28 '09 at 22:53
add comment

21 Answers

up vote 33 down vote accepted

-- Phil Karlton

Coming up with a good name for a table that represents a many-to-many relationship can help a lot in decreasing complexity of the data model. Sometimes it is not easy but it pays off to spend some time thinking about it.

An example:

Reader and Newspaper. A Newspaper has many Readers and a Reader has many Newspapers. You could also call their Relationship a Subscription instead of NewspaperReader. This way it also feels more natural if you want to add attributes to the relationship or want to map objects to the table 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 (so ColourShape would be alright in your case) though I think Nick D posted great suggestions with Style and Texture.

share|improve this answer
+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
"There are only two hard things in Computer Science: cache invalidation, naming things, and off-by-one errors" –  Neil McGuigan Aug 9 '13 at 1:25
add comment

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


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.

share|improve this answer
I too use _XREF... it's always made sense to me. –  Tenner Nov 29 '09 at 4:29
A personal favorite. –  Mark Canlas Nov 30 '09 at 19:47
add comment

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.

share|improve this answer
Bill, thanks. Very interesting answer. –  devuxer Nov 28 '09 at 19:18
@Bill: All about the synonyms, and the synonym preference impacts the naming convention. –  OMG Ponies Nov 28 '09 at 19:23
I suppose HasColor could be another possible name for the intersection table that uses natural language. –  Bill Karwin Nov 28 '09 at 19:35
@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
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
show 1 more comment

How about ColorShapeMap or Style or Texture.

share|improve this answer
+1 for coming up with something more elegant than just joining the names of the tables –  tosh Nov 28 '09 at 21:01
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

A mapping table is what this is usually called.

share|improve this answer
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
In this case, any shape could be matched to any color, so it's many-to-many. –  devuxer Nov 28 '09 at 18:48
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
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
add comment

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...

share|improve this answer
But what would you name the actual table? –  devuxer Nov 28 '09 at 18:56
Or 'association table'. –  Jonathan Leffler Nov 28 '09 at 19:05
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
add comment

Intermediate Table or a Join Table

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Maybe just ColoredShape?

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

share|improve this answer
General guidelines. –  devuxer Dec 16 '09 at 18:11
add comment

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

share|improve this answer
add comment

In adiction to what Developer Art has related,


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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Call it a cross reference table.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


