Modify Table SET/ENUM query?

[neoform]

I'm having trouble finding the query that allows me to add/update/drop the SET/ENUM values without simply resetting it by redeclaring the values (which in turn destroys all the data for that column in the table).

[fenway]

SETs are very annoying... with  ENUM, as long as you add to the end, your data should stay intact.

[neoform]

they are, but when dealing with user permissions, I'd say it's one of the best options. takes up something like 4 bytes and this way user perms don't have to be stored in a separate table, I can have them right in the users table.

[fenway]

they are, but when dealing with user permissions, I'd say it's one of the best options. takes up something like 4 bytes and this way user perms don't have to be stored in a separate table, I can have them right in the users table.

I entirely disagree -- use a BIT field if you really want to save space.

[neoform]

well, considering my permissions rarely change, what's the real harm?

 

This way every user is given the same access to the permissions.

 

Before i switched to using SET, i had to have three tables, users, link, permissions

 

The link table, had two columns, user_id and permission_id.

 

When you added up all three tables, they not only took up more disk space, but in order to get a user's permission along with permission name, I'd have to join all three tables, which is significantly slower than just selection the permission from the users table if it were a SET.

 

The only downside i've found with SET, is when you need to change or delete a permission type, it's requires updating the whole table, as well I'm limited to 64 permission types (not really that bad).

[fenway]

You can see here -- I still wouldn't recommend it.

[neoform]

I'd agree with you, but think about it like this.

 

I always want all permissions for a given user, i never index or sort by permissions, nor do i select individual permissions, I need each and every one for a given user. a set does that perfectly and it takes up the bare minimum amount of space. Using 3 tables would take up at least four times as much space.

[fenway]

IMHO, that's short-sighted -- and as I said before, a SET is just a haphazard implementation of a BIT mask.

[neoform]

How is it shortsighted though?

[fenway]

How is it shortsighted though?

I can think of lots of reasons why I'd need to access individual permissions.

[neoform]

Well, keeping in mind, my site has a user authentication process whereby every page load checks the user's credentials before anything else, and in the process, selects all of it's access permissions and stores them in a global variable for any part of the site to use/access.

 

When you have a situation like this, I would never need to go digging into the table again to find out what permissions that user has, unless I'm actually modding the user's permissions.. in that case I'm still grabbing them all, not going one by one.

 

If I did want to have a page that shows all users with a given set of permissions, I could do that, but the number of times that page gets accessed vs. the user going through an auth process is tiny and not worth worrying about speed issues due to lack of indexing or whatever..

[fenway]

I was referring to the situation where you would need to specify additional info about each permission, not just y/n.

[neoform]

That's why I will actually have a separate permissions table that matches it. The only difference is I don't need to call it for the authentication process, which makes the whole site faster.

[fenway]

That's why I will actually have a separate permissions table that matches it. The only difference is I don't need to call it for the authentication process, which makes the whole site faster.

The final decision is yours...

[neoform]

well, what would be your suggested means?

 

here's the table as I currently have it

 

+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field               | Type                                                                                                                                                                      | Null | Key | Default | Extra          |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id                  | mediumint( unsigned                                                                                                                                                     | NO   | PRI | NULL    | auto_increment |
| permissions         | set('admin','root','statistics','emulate','stealth','user_editor','ban','control')                                                                                        | YES  |     | NULL    |                |
| username            | varchar(32)                                                                                                                                                               | NO   | UNI |         |                |
| email               | varchar(100)                                                                                                                                                              | NO   | UNI |         |                |
| password            | varchar(255)                                                                                                                                                              | NO   |     |         |                |
| hash_type           | varchar(64)                                                                                                                                                               | NO   |     |         |                |
| salt                | varchar(255)                                                                                                                                                              | NO   |     |         |                |
| status              | enum('banned','deactivated','active','notice','pending','user_deactivated','user_deleted','deleted')                                                                      | NO   |     | active  |                |
| created_on          | datetime                                                                                                                                                                  | NO   | MUL |         |                |
| email_expires_on    | datetime                                                                                                                                                                  | NO   |     |         |                |
| password_expires_on | datetime                                                                                                                                                                  | NO   |     |         |                |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+

 

I also have a permissions table with some additional info for the admins that matches the name.

 

Keeping in mind that I select all permissions for a given user on every page load, how would you do it?

[fenway]

As I mentioned before, my preference is a separate permissions table... if it's indexed by user_id, the cardinality is good, and it should be relatively fast to get back a few permission records; then it gives you room to grow.

[neoform]

That's actually how I had it until a few days ago..

 

I think having it as a SET is better, mainly cause I have 3 less queries in my security.php file and I still have all the same functionality, the only difference is, now I have a 64 permission limit.

 

My main concern is what happens when I get 1,000,000 users. If each of those users have a permission, that permissions link table will be very very large, both in row count, and in size.

[fenway]

1M users? You'll have other issues by then.

[neoform]

That might be, but I'm building a general framework. I want to limit the number of bottlenecks that I can run in to down the line.

[fenway]

That might be, but I'm building a general framework. I want to limit the number of bottlenecks that I can run in to down the line.

The most "general" is to have full control over the permission records -- which requries them to be records.

[neoform]

I actually have a backend that allows you to not only set a given user's permissions, but create new permissions as well..  even with the SET() ..

[fenway]

I actually have a backend that allows you to not only set a given user's permissions, but create new permissions as well..  even with the SET() ..

This is a pointless argument... you can't JOIN a permission from inside a SET.

[neoform]

Well what it comes down to is application.

 

I'll never join the permissions table while using set, not because i can't, but because i don't need to.

[fenway]

Well what it comes down to is application.

 

I'll never join the permissions table while using set, not because i can't, but because i don't need to.

Because you don't need to *yet*... what if you want to temporary grant a permission?

[neoform]

When would I need to do that?

 

I'm honestly trying to look as far forward as possible here, but I really haven't found many downsides to using SET for this project..