I have two tables:

• user which contains all my users
• user_status which contains all possible statuses a user can have. There are also other statuses (such as order statuses, so simplifying this table's name to just status is not an option)

How would I go about naming a table which maps users to their status?

• I was considering user_user_status but that seems a bit unclear

• I know I could just link the two via a foreign key in the user table, but I want to keep the database as normalized as possible -- and I can foresee the possibility of having to track the history of a user's statuses

CodePudding user response：

This does not really answer your question, but I have to post this anyway.

You are asking about a many-to-many (M-M) situation, but you admit that your data is really one-to-many (1-M), that is, a user can have one and only one status. Yes, you could model this with M-M, and yes you could use this to track history (how user status changed over time), but in the long run this will saddle you with needlessly overly complex structures that will be at best awkward to work with. (Been there, done that. Trust me on this.) I would strongly recommend having tables User and UserStatus, with column UserStatusId as a column in table User, and add in table UserStatusHistory when (not if!) you need to track how it changes over time.

Ok, with that said, if a user could be assigned multiple user statuses at a time, I’d do

User

UserStatus (no underscore, save them for when you need them)

User_UserStatus (such as now)

(I would not do UserStatus_User, because presumably User is the more “significant” entity in the model.)

CodePudding user response：

You can name a table whatever you want.

Can a user only have one status at a time? Do you need to hold the history of the statuses a user has had? Is the only attribute in the status table the status value? If not, what other attributes are there in this table?