Best Practices for PCs in SQL Server

Question

I was wondering what best practices or forks are in setting up a PC in the M2M table in SQL Server. For example:

I have 2 tables

• Users
• Roles

I create a new table

• UserRole

Who has 2 RoleId and UserID fields

now i have to

• create UserRoleID as PC and make UserID and RoleID FKs
  • make PK UserID AND RoleID and set them as FKs
  • something else

I would like to know the performance issues with each of the options and recommended recommendations.

Answer 1

The standard procedure for these cases is to have two indexes. Unique PK - these are two fields: a composite with a field with a higher power, that is, a user identifier; and a second index with only a secondary field (i.e. with a RoleID).

Then the cluster, depending on what is most likely to be involved in many sets with many speech signals (i.e., when requesting multiple roles per user or multiple users per role).

Answer 2

Declare PK as (UserID, RoleID). (Note: order is important)

Declare UserID as FK with reference to the Users table. Declare RoleID as FK with reference to the Roles table.

In any case, your DBMS will give you a composite index in (UserID, RoleID) in that order.

With any luck, this will speed up the association between users and roles. A good DBMS will give you a merge join for a join without any restrictions other than a join condition. A three-way connection should work fairly quickly, assuming that the number of roles is small.

When you join UserRoles and roles without joining Users, you may find that it is disappointingly slow. How often do you do this and how important is speed in this case? If this is important, you can create an index only for RoleID.

Answer 3

It depends on whether you want to hang any other value in that a particular user has a specific role. If not, just create a clustered PK to scroll through two fields.

Add an FK for both and add an index to the second field. Give some consideration to the order in which the fields should appear. Most likely, you will receive a set of roles belonging to the user, or a set of users in a specific role?

Answer 4

It depends on how you use them. In most cases, I make a primary key as UserId and RoleId to make sure they are unique. The value of the same user cannot have the same role.

Now this means that "depends" is included in the game. If you are going to associate the UserRole table with another table, this is where I create the primary key UserRoleId. And make UserId and RoleId a unique limitation.

The reason for this is not that the table that references UserRole has both UserId and RoleId when it is not needed, because you are referring to UserRoleId and not to the User table and role table, respectively.

Answer 5

Avoid composite PKs and put a unique index on two FKs (this seems to be the case.). Not a problem in this case, but should be consistent. Do not forget to refer to several fields to join them when writing queries, this is a pain. If your composite key should consist of datetime, char or other types of fields, performance becomes a hit.