What's SQL table name for table between 'Users' and 'UserTypes'?

i have two tables in my database :

• Users : contain user information
• UserTypes : contain the names of user types ( student , teach开发者_Go百科er , specialist ) - I can't rename it to 'Types' as we have a table with this name

relation between Users and UserTypes many to many .. so i'll create a table that have UserID(FK) with UserTypeID(FK) but I try to find best name for that table ... any suggestion please ?

I would recommend following whatever naming conventions that your coding guidelines - you do have on right? - state for these types of tables. If you don't have a guideline, then something like this might work:

Users_UserTypes

I think a good practice would be to call it something like "Users_UserTypes", possibly prefixed with some conventional codeword you invent for this purpose. That's what happens in my application, anyway.

Do you have anything other the "type" in the UserTypes table? How many entries are there? If there's only a few, and it doesn't contain any additional data, then I would simply denormalise that table and don't have a separate joiner at all.

That is:

Users:

  ID       |  First Name      |  Last Name
-----------+------------------+--------------------
 123       | John             | Smith
 456       | Jane             | Doe

UserTypes:

  UserID   |  Type
-----------+----------------
 123       | student
 123       | specialist
 456       | teacher

The "Type" column doesn't have to be a string, it could be an integer that's mapped to an enumeration in your code or whatever you like.

IMO, there's not much point denormalising things to the point where you've got 500 tables all joining together all the time...

Another approach (besides Users_UserTypes) would be UserTypes_for_Users It's a bit less conventional but IMHO more understandable

User, UserType (I name every table singular) and... UserTypeMap

We use an Table1_x_Table2 naming convention for many-to-many tables.

This makes things quite clear, and indicates the cross-reference nature of the table: Person_x_Company Package_x_Product User_x_UserType

For self-relation tables, such as employee-to-manager, we have a Person_x_Person table with a "relation_type" and "priority" column, to allow multiple managers per employee with a primary versus secondary manager.