Database table naming; name_verb_name conventions for many-to-many intersect tables

Question

I've read around on SO for other questions on the subject, and I'm aware that naming conventions are an often subjective topic, however I feel this is less general (and less inquired about) than PascalCase versus under_scores.

Given a simple commerce system, with User and Product entities; a given user can purchase items, and the transaction records are stored in a User_Product table. (sure, Purchase is more semantic, but just follow me here)

Anyways, if I were to introduce the ability to "Vote" on products, and "Favorite" products, no longer does the User_Product scheme remain unambiguous.

Is it an uncommon (and therefore I would assume less acceptable) naming scheme to use Name_Verb_Name as follows:

• User_Purchase_Product
• User_Vote_Product
• User_Favorite_Product
• and so forth...

I haven't had the opportunity to work on any "enterprise level" systems in my work, so in my personal development I'm fraught with questions about naming schemes, trying to adopt the most prevalent.

I understand that consistency of use typically trumps the semantics of the convention chosen, but again; trying best to standardize my conventions as best as possible.

(note that as I mentioned, Purchase is likely a better choice semantically for the above, but I'm curious more generally, when such semantics may not exist or be concise enough)

Answer 1

Naming conventions are not standard-less!

Science is not subjective!

There are Standards, and there are reasons for Standards. All those issues have been answered, and standardised, over 30 years ago. Do read my IDEF1X Introduction, IDFE1X is the Relational Database Modelling Standard, there is no other.

First, there are no 'Intersect' or 'link' tables, each table has a specific purpose which is determined during the modelling exercise, and it is not to link tables or intersect paths. When you take that formal approach, the tables and their meaning is naturally exposed.

Second, if you want a Relational Database, you need to implement Relational Keys or Relational Identifiers, as per the Relational Model. The Relationships are Identifying or Non-identifying. That means no surrogates. Tables are not isolated two-dimensional creatures, once those Identifying Identifiers are determined, the logical or third dimension becomes clear.

Generally that means compound keys.

Third, if you use the IDEF1X standard during modelling, you will have Verb Phrases (detailed in the link). These identify the relationships between the tables very clearly. Again, 'intersects' and 'links' are eliminated, you have proper names and meaning.

Consistency of use is irrelevant, consistency of data, of meaning, is relevant. Model the data, not the usage.

Therefore, when you get to the point where you are evaluating the issue in your question, the naming required is clear; it is an extension of what is already determined and modelled. There is no need for three-part names, two-part names are perfectly enough.

• Given your User and Product tables

• UserProduct is not a complete name per reasoning above

• let's say they were Independent

• the Favourite table would be Dependent on both
• its PK would be the UserPK plus the ProductPK
• the Verb Phrase is Each User Favours zero-to-many Products

• I would name it UserFavouredor UserFavourite

• User_Favourite_Product is overloaded, the third part is redundant

• Likewise, Each User Elects zero-to-many Products (or Supports or Elevates)

• its PK would be the UserPK plus the ProductPK (unless you like duplicates)
• Therefore UserElected or UserElection

The above are Associative Tables (what you call 'link') or a few columns added to same. Purchase is substantially different (not a 'link'). Various details must be recorded for a Purchase, that do not exist in either User or Product.

• I would name it Purchase
• UserPurchase is redundant and silly
• UserPurchaseProduct is silliness squared

---

(I was maintaining the limits of your question. In reality, there would not be a Purchase table, there would be a SalesOrder which forms the basis of the Purchase, a commercial event, and SalesOrderItem, which details the Products therein.)

Answer 2

You should pick a convention and be as consistent as you can be, as Oded pointed out.

One the of things that I have always found is that standards are rules of thumb that try to institutionalize common sense. Therefore, it isn't just enough to ask what your standard is, you need to ask what it buys you. A lot of the time, one of the best advantages of any particular standard is that sticking to it gives your system consistency - which is valuable because it makes the system easier to understand (at least once you get used to the conventions that have been used within the system).

If your naming convention is ultimately aimed at making your system understandable, then wouldn't it be better to pick names that can be easily understood?

This is where we step off into the realm of pure opinion, but PURCHASE seems to me to be a little easier to understand than USER_PURCHASE_PRODUCT. My rule of thumb is to use plain language that most people would understand when naming tables - especially tables that contain data about entities that are significant to the business. If plain, general public language won't do, then use some business specific jargon (although this can be subject to change as the business evolves).

@Bracketworks, you asked about cases where there are no obvious business names or where names aren't precise enough. I'm not sure that this "problem case" isn't self-imposed. I think that there will always be a sensible name for any table. Sometimes you have to think hard about it. A lot of the time you will make your life easier if you remember that a table's name needs to make sense within the context of the database where the table lives (and not necessarily outside of this context). You should have some documentation somewhere - like a system support wiki - that gives you all the room you need to explain the semantic intricasies. The table name only has to be sensible enough to be clear within the context of the system where the table lives and works.

As to noun_verb_noun, I myself wouldn't choose a naming convention that emphasized mechanical consistency without any regard to how it impacts the understandability of the components of the system. However, as always, the great thing about standards is there's so many to choose from!

Answer 3

There is no "standard" and this is entirely subjective.

As is usual with these things - pick one and stick with it in any one project.