Table naming conventions for ref tables vs storage tables

Question

By ref table I'm referring to a kind of entity 'type' table, and by storage table I'm referring to a table that stores a lot of changing information.

For example:

I have a 'user' table named as such, which is a storage table since it can hold an indeterminate amount of users.

Then i have 'roles' table, which holds role information, it is a type table, as there are many users for each role.

I then have a 'profiles' table, which hold a one to one relationship with the 'user' table.

Now, I've tried this:

• user
• userrole
• userprofile

However, this convention to me implies that the roles and profile tables each have a one to one relationship with user whereas I know the role table does not.

How to people usually name tables for semantic purposes for the example I described?

Answer 1

The Oracle convention works well here.

• plural for normal tables (Users for a table of Users. The table itself is a store of many users, so I name it as I would describe the data within it)
• User_Roles would be a distinct list of roles.
• User_Role_Assignments a list of roles for users. I would imagine you would want a many to many here if a user can have any role and a role can be assigned to any user.
• User_Profiles would be a distinct list of profiles.
• User_Profile_Assignments for a table which was a many to many relationship between users and profiles.

If you have a one to one relationship, then one of the tables should have a key to the other. Users should have a profile_id if there is a one to one relationship.