What should I name a table that maps two tables together? [closed]_问答_开发者

Closed. This question is opinion-based. It is not currently accepting answers.

开发者_如何学编程

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 5 years ago.

Improve this 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

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.

How about ColorShapeMap or Style or Texture.

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.

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.

A mapping table is what this is usually called.

ColorToShape
ColorToShapeMap

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.

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.

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.

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

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.

Intermediate Table or a Join Table

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

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

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

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.

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

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.

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.

Maybe just ColoredShape?

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

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

In adiction to what Developer Art has related,

ColorShape

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

Call it a cross reference table.

XREF_COLOR_SHAPE
(
     XCS_ID INTEGER
     C_ID   INTEGER
     S_ID   INTEGER
)

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

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.

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.