> 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.
Your math, of course, assumes that every user has a match with every permission value, which seems highly unlikely. But in any case, since you said earlier that your permissions are binary values (you represent them with checkboxes), why not represent them using bit values? That will provide you with the advantages you claim (except to a greater degree, since bit values require less storage than byte values) without the disadvantages you mention. In addition, it may provide you additional advantages, such as the ability to use bitmasks to handle permissions at runtime. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235690 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
