Proper Table naming convention for a many-to-many intersect table

I would prefer "Clients_Brokers" (pluralizing both names to indicate many-to-many).

I usually use the names of both of the joining tables.

So, in your case, ClientBroker.

I prefer to distinguish between intersect tables and actual transactional tables. So, I end them with Map. So it would be Client_Broker_Map, or ClientBrokerMap.

I've often seen the format "Client_Broker"

Some programmers don't like pluralizing table names for a few reasons:

• it breaks the "is a" rule, meaning if you have a table called 'User', then each record in the table "is a" User object. This follows object-orientation rules.
• a Model class is usually named for the table where its data comes from. So if you have a User model, the record represented by the model is in the User table

This makes a lot of sense if you're in control of the entire db and business layers of the project. However, a lot of frameworks now have ORM libraries that facilitate working with tables and relationships. Those ORM libraries often have a naming syntax that should be followed to let the ORM library do most of the heavy lifting.

For example, I use the Kohana MVC framework for PHP which offers an ORM library. The ORM suggests pluralizing table names, using all lowercase names, and using underscores for many-to-many table names. So for your example, you'd have the following tables: clients, brokers, and brokers_clients (the ORM suggests alphabetically arranging table names for many-to-many tables). When creating models for these tables (extending the ORM model), you use the singular value of the table name, so the model for clients would be Client. The ORM handles the pluralization conversion. Kohana's ORM also uses an inflection library, so unusual pluralization values are handled correctly. For example, a table named 'categories' can use the model name 'Category'. Finally, if you have a db structure already implemented but want to use the ORM library, you can override the default ORM table naming syntax and give it the table name you want to use.

For Hibernate users, there is undocumented rule found by trial and error based on log messages and analyzing generated queries:

Formalizing this picture, you need to use a linking table named : table1_table2 and use ids in linking table like these ones: table1_table1Id and table2_table2Id

By doing so, Hibernate understand what's going on without further explanation and providing @JoinTable info.

@Data
@Entity
@Table(name="course")
public class Course {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO, generator="native")
    @GenericGenerator(name = "native", strategy = "native")
    private Long courseId;

    @Column(name="name")
    private String name;
}

@Data
@Entity
@Table(name="student")
public class Student {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO, generator="native")
    @GenericGenerator(name = "native", strategy = "native")
    private Long studentId;

    @Column(name="name")
    private String name;

    @Column(name="class")
    private String className;

    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private List<Course> course;
}

If you stick to this mapping, all the JPQL queries work smoothly and as expected generating correct SQL queries with outer joins... Note, course without -s at the end! If you want courses anyways you should change fields name in joining table to table2s_table2Id and table itself to table2s

P.S. I haven't found any straightforward mentioning about this convention neither in JPA nor in Hibernate documentation (only in single example), and it can probably be changed in future releases, tested with Hibernate 5.2/5.3. However it gives a hint of how to name joining table and fields in it.

I prefer junction table names that explain the relationship between the entities in the table. In this instance, I would either call the table "client_has_broker" or "broker_has_client".

Aside from making it clear what the relationship is (things like "client_broker" and "broker_x_client" don't tell you how the entities are related - does the client work with the broker, or did the client eat the broker?), this allows you to have other junction tables that relate the same entities in different ways - like, "client_hates_broker", or "broker_bankrupted_client".

As for the singular-vs.-plural issue - even though this is a many-to-many relationship, each row in the table still only refers to a single relationship, so I always use singular names (e.g. "client_has_broker", not "clients_have_brokers"). That way the name makes sense no matter how many results you get from a query. ("This client has these 5 client_has_broker relationships" makes more sense to me than "this client has these 5 clients_to_brokers relationships".)

Obviously there are a million different approaches - and I've tried a bunch of them - but for me, this has been been the easiest and clearest naming convention.