0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Flosculus
  • 6,880
  • 3
  • 18
  • 42

1 Answers1

0

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.

dseibert
  • 1,319
  • 9
  • 19
  • i made an edit to my question, as roles has a one to many with users, not many to many, my mistake. also, regarding: Roles would be a distinct list of possible roles. I would still like the table name to indicate that its a list of 'user' roles, as there may be other entities with the term 'role' will apply to – Flosculus Oct 27 '12 at 22:40
  • do i really need junction tables for this? – Flosculus Oct 27 '12 at 22:43
  • User_Roles could be used in this case. If you eventually want a table to link User_Roles and Users I suggest something like User_Role_Assignments. Now that I think about it, I like to imagine you were describing all items in the table when choosing a name. – dseibert Oct 27 '12 at 22:44
  • which level of normalization is this? i had intended for users to only hold IDs, one of them being a role, and as the profile will not be required 'immediately' upon user creation, but afterwards, would hold the userid, opposed to the user holding the profileid. so far there are no many to many relationships required for this. – Flosculus Oct 27 '12 at 22:48
  • If each user can only be assigned one role and each user can only have one profile you'll want to put the profile_id and role_id inside the Users table. I was imagining the scenario where you would want multiple roles and profiles associated with an individual. – dseibert Oct 27 '12 at 22:51