What should I name a table that maps two tables together?
SqlDatabaseDatabase DesignNaming ConventionsData ModelingSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
> 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.
Solution 2 - Sql
How about ColorShapeMap or Style or Texture.
Solution 3 - Sql
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.
Solution 4 - Sql
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.
Solution 5 - Sql
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.
Solution 6 - Sql
A mapping table is what this is usually called.
ColorToShape
ColorToShapeMap
Solution 7 - Sql
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.
Solution 8 - Sql
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.
Solution 9 - Sql
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
Solution 10 - Sql
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.
Solution 11 - Sql
Intermediate Table or a Join Table
I would name it "ColorShapes" or "ColorShape", depending on your preference
Solution 12 - Sql
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...
Solution 13 - Sql
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
Solution 14 - Sql
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.
Solution 15 - Sql
"Many-Many" table. I'd call it "ColourShape" or vice versa.
Solution 16 - Sql
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.
Solution 17 - Sql
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.
Solution 18 - Sql
Maybe just ColoredShape?
I'm not sure I get the question. Is this about this specific case or are you looking for general guidelines?
Solution 19 - Sql
I would name it with the exact names of the tables being joined = ColorShape.
Solution 20 - Sql
In adiction to what Developer Art has related,
ColorShape
would be a usual naming convention. In ER diagram, it would be a relation.
Solution 21 - Sql
Call it a cross reference table.
XREF_COLOR_SHAPE
(
XCS_ID INTEGER
C_ID INTEGER
S_ID INTEGER
)
Solution 22 - Sql
I'd use r_shape_colors or r_shape_color depending on its meaning.
r_ would a replacement for xref_ in this case.
Solution 23 - Sql
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.
Solution 24 - Sql
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.