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

Question

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

Answer 1

A mapping table is what this is usually called.

ColorToShape
ColorToShapeMap

Answer 2

Intermediate Table or a Join Table

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

Answer 3

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.

Answer 4

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

Answer 5

In adiction to what Developer Art has related,

ColorShape

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

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

How about ColorShapeMap or Style or Texture.

Answer 11

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.

Answer 12

Call it a cross reference table.

XREF_COLOR_SHAPE
(
     XCS_ID INTEGER
     C_ID   INTEGER
     S_ID   INTEGER
)

Answer 13

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.

Answer 14

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

Answer 15

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

Answer 16

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.

Answer 17

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.

Answer 18

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

Answer 19

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.

Answer 20

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.

Answer 21

Maybe just ColoredShape?

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