neoform Posted October 23, 2007 Share Posted October 23, 2007 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). Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 SETs are very annoying... with ENUM, as long as you add to the end, your data should stay intact. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 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. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 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). Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 You can see here -- I still wouldn't recommend it. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 IMHO, that's short-sighted -- and as I said before, a SET is just a haphazard implementation of a BIT mask. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 How is it shortsighted though? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 How is it shortsighted though? I can think of lots of reasons why I'd need to access individual permissions. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 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.. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 I was referring to the situation where you would need to specify additional info about each permission, not just y/n. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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... Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 1M users? You'll have other issues by then. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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() .. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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? Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 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.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.