designing database tables using JDBC

Question

I am creating a users table using JDBC and mysql, each user has a permissions list that comprises Integer values.

I am wondering if I should use an array for storing these values and then have only 1 record for this user in the table, or simply create a new table that comprises 2 columns: user ID and permissions, and then have multiple records for each user that specify the user name in one columns and one permission value in the second column.

the second option seems to be redundant since a permission value is a simple object that isn't associated with any other objects (like a student and courses list for example, because the course is associated with many other objects, like grade, teacher, etc, so in this case it is natural to have multiple records), but the first one seems to be a bit unnatural to me, so if someone has experience with these things and can direct me to a "natural" design? thanks

Answer 1

If you want to store multiple permissions for a single user I would properly use three tables. One for the users and one for the permissions. It's bad design to store multiple informations i a single attribute (ex. comma separated). To store multiple informations will give you a lots of limitations.

1. It's not possible to sort the attribute sensible
2. It's no possible to use the attribute to join tables
3. And probably more

user

idUser
name

permission

idPermission
permission

userPermission

idUser
idPermission

Answer 2

If you find yourself thinking about storing a comma separated list (or whatever separator) of values in a column, this is a very strong hint that you should use another table (actually two, since what you have is you have a many to many relationship). Not doing so breaks normalization.

That being said, I've seen (old) systems that were storing a list of permissions as "a permission mask" (e.g. '101101'). But I think it's a poor practice that goes against the above rule and don't have a good argument to justify it (except that you avoid joins... which aren't a problem if you don't do 7+ of them).

Answer 3

"This isn't what I need, like I said, permission is only an integer value (no name or id). I'd like an answer that can walk me through my question's details"

Consider the following statements

• User noona has permissions 5, 6, and 7
• User apc has permissions 1, and 9

Which is the more powerful user? What can user noona do with permissions 5, 6 and 7? What do those permissions mean?

Without storing a name or identifier the meaning of the data exists outside the database. There are occasions when this is valid but usually it is an indicator of a poor design decision. That's why everybody is pushing for a Permissions table and a UserPermissions as an intersection table between that and Users.

Having a separate UserPermissions table is the right choice (regardless of where you store the meaning of permissions) because having one column for the value makes it easy to find all users who have permission 4 or to query whether user noona has permission 7.

In any event, arrays are unnatural.