Munson, Jacob wrote: > > This is the kind of thing I will create a separate table for. For > example, using your example, you would have a permissions table. All it > would have is the userid, a permission id, and the permission > description. Then you just query that table using the userid and get > all records returned. If you want them in a list, use ValueList().
That sounds a lot more complicated than what I do. Extra queries, extra joins and *MORE DATA STORAGE* Let's say, for example, your user ID is a UUID... 35 characters. Plus your permission ID, which we'll say is a tinyint (limiting you to 255 different permission ids) and that each flag value is also a tinyint. Let's also say that the possible values are 0-9. So for 100 flags, you get the UUID 100 times plus two tiny ints.. equivalent to 102 bytes of storage Per flag... so that's 10,200 bytes per user. If you have 10,000 users, that's about 100 meg of data. Using my method, I can store all 100 flags in a single varchar(255) with some room to spare. Plus the UUID that is already in the table so it does not count. For 10000 users, that would be less than 2 megs of storage (100 flags, 1 character each plus a comma in between = 199 characters/bytes * 10,000 users / 1024 / 1024) My method has the following advantages in this case: - cuts storage space by 98% - requires no extra tables - dramatically simplifies queries. Disadvantages: - can't query on specific permission flags - not easy to remove permissions on a database wide basis via SQL (ie in your case you can "delete * from permTable where permid=5") > You probably say, "What a waste of time, my method works and doesn't > have any problems!" Well, you never know when there will come a time > that you wish you weren't storing those fields as a delimited list. > Remember how people in the 70's and 80's thought they'd never need to > store more than a 2 digit year? Also, remember how Bill Gates thought > we'd never need more that 640k of RAM? :) Yeah, I remember that. We somehow adapted =) Back in 1930, when Duke University built Cameron Indoor Stadium (the basketball arena), people laughed and said "You'll never get 10,000 people to go see a basketball game!". Go figger! Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235689 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
