Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

For 3 tables that are in 3NF, a many-to-many relationship, is there a standard name or term to describe the 3rd table, the one that associates the other tables? Basically, I'm looking for a semantic way to name the 3rd table.

Example

You have two tables: users and widgets. A user can have multiple widgets and a widget can be owned by multiple users. What is the most semantic name for the 3rd table?

In the past, I've used the following at one time or another:

  • userWidgets
  • userWidgetAssn (association)
  • userWidgetXref (cross-reference)

In my example, userWidgets is probably my top choice, but it doesn't always work as cleanly depending on the table names. Is there an industry standard?

share|improve this question
    
Is there an industry standard? Probably not, as with most naming issues. Other than being consistent, of course. I'd go with user_widgets as well, since user is the more important of the two entities, from a business wise perspective. –  Yannis Rizos Feb 4 '12 at 23:42
add comment

3 Answers

up vote 3 down vote accepted

I hope you could bear with me in the next few lines. I don't agree with the naming conventions you have provided. Data Modeling has a purpose of making us think in more detail about the business and not only generate DDLs. If we use the effort and time spent in constructing the model to learn more about the business we could gain a lot. Now you say:

A user can (have) multiple widgets and a widget can be owned by multiple users.

To me, using the word have is not in fully in sync. with the word owned.

If you insist on the part "a widget can be owned by...", you need to say a "a user could own 0,1 or more widgets". This is to make it clear that we are dealing with 1 association (or relationship) in both directions.

Analyzing this further, what does it mean to say "a user owns a widget"? It is not very clear to everyone. Did the ownership occur as a result of a buying process? (as in: user buys a widget) or a granting process? (as in privilege granting)? If the ownership occurred due to a buying process, then the intersection table between the widget and the user is the table capturing the buying history (or buying details) of the user.

Now you can decide if you want to use a descriptive name such as "UserPurchaseDetail" (a better name is suggested below by @Joel Brown) or only use a name like UserWidget.

Again, whatever your choice of the name is, my point is that one should always strive to do more analysis and use a name that reflects what is the table used for in business terms as much as possible.

Edit - Added name suggested by the note below as an alternative name.

share|improve this answer
    
The example really was meant to be generic, I just couldn't think of anything better at the moment. –  VirtuosiMedia Feb 5 '12 at 7:34
    
@VirtuosiMedia, thanks for your comment, I hope that the answer added value. –  Emmad Kareem Feb 5 '12 at 8:23
    
+1 for making the intersection table name meaningful instead of just robotically applying a convention used by unimaginative people. Consistency is not an end in itself. It is a means of avoiding confusion. Since avoiding confusion is the goal, why not avoid even more confusion by picking a name which adds meaning? –  Joel Brown Feb 5 '12 at 18:56
    
@Joel Brown, thank you for your comment, please make a suggestion. –  Emmad Kareem Feb 5 '12 at 20:22
    
@EmmadKareem I think your suggestions were spot on. In light of OP's description, all we have to go on is "widget is owned by user" so I might use WidgetOwnership. –  Joel Brown Feb 5 '12 at 20:54
show 1 more comment

I've seen just using the two. Just keep the same standard you use for everything else.

If your tables are users and widgets and you use camelCase, then userWidgets is fine. (I prefer that to usersWidgets because each userWidget is an individual item) The key is to be consistent every time you do it. This is more important than which standard.

I've seen it elsewhere with customer and product, and it's essentially the same thing you've described.

share|improve this answer
    
+1 on consistency. I'm just trying to find out what I should be consistent with. –  VirtuosiMedia Feb 5 '12 at 7:35
    
@VirtuosiMedia - You need to be consistent with yourself. It's very smart to ask what standards are out there. The next important step is to consistently follow it. 100% adoption of a good standard is better than 70% adoption of a perfect standard. Internal consistency is what's important. I've been in several places where people argue the minutia of standards to death, and then don't follow the "Perfect Solution" that they come up with. –  MathAttack Feb 5 '12 at 14:57
add comment

userWidget Singular. (I dropped the s). This is the closest to a standard you will find.

Adding Xref or Assn provides no additional information.

Can you expand on why you went singular rather than plural?

Because when using an ORM I will be referring to userWidget and a Single Object (record) from the userWidget table and userWidgets to refer to a collection of those objects. It's cleaner.

share|improve this answer
    
Can you expand on why you went singular rather than plural? –  VirtuosiMedia Feb 4 '12 at 23:49
1  
Can you provide proof of this being anything close to a standard? The most common name I've seen is users_widgets, but I've seen many variations, including users_widgets_assn. –  Thomas Owens Feb 4 '12 at 23:52
    
@Thomas No, I can't. (I don't believe there is one). This is based on my experience as the most common. I too have scene a lot of variation with this –  Morons Feb 5 '12 at 0:03
    
@Morons Then why are you referring to it as "close to a standard"? There is no standard, and I don't think there is enough information available to say what is the most common across all projects. –  Thomas Owens Feb 5 '12 at 0:05
    
I am referring to it as "close to a standard" because it not a standard and my own anecdotal non-scientific evidence suggests it's the most common across all projects . –  Morons Feb 5 '12 at 0:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.