General > Developers Corner
UCL to SQL Discussion
roastchicken:
--- Quote from: Megiddo on February 09, 2016, 02:40:09 PM ---As with all "best software/database practices", you have to draw the line somewhere. Sometimes utility trumps sexy.
I personally have no problem with appropriate use of nulls. I believe this is an appropriate use.
--- End quote ---
One possible solution that I just thought of is to have two tables, one for access strings and one for access tags, but this is pretty clumsy and it seems like a lot of work just to follow some normalization rules. Plus the names of the tables get pretty out of hand. "ulib_user_allowed_access_strings" and "ulib_user_allowed_access_tags" :o
edit:
I've been working more on normalizing the database schema, but I think I'm going to stick with this schema that is normal form one (lowest level of normalization):
ulib_users:
Storage ClassINTEGERINTEGERTEXTTEXTColumnsteamid64uniqueidnamegroupExample Value765611980415442891164255280roastchickenoperator
ulib_groups:
Storage ClassTEXTTEXTTEXTColumnnameinherit_fromcan_targetExample Valuevipuser!%operator
ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx ban
ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx banid
ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXTColumngroup_nameaccess_stringaccess_tagExample Valuevipulx votekick
The table names are a bit clunky but I think it's best that they are this clunky so they stay descriptive.
In order to normalize it to any higher forms (such as BCNF that Megiddo suggested), all the access strings and tags would need to go in their own tables that only contained access strings/tags. That seems extremely wasteful to me, and I don't see any possible benefits either.
Oh, and the reason there aren't any access tags is because I forgot how they work :P
roastchicken:
(this post is just for reference, in case we want to revert to a previous database schema)
New Schema:
ulib_users:
Storage ClassINTEGERINTEGERTEXTTEXTColumnsteamid64uniqueidnamegroupExample Value765611980415442891164255280roastchickenoperator
ulib_groups:
Storage ClassTEXTTEXTTEXTColumnnameinherit_fromcan_targetExample Valuevipuser!%operator
ulib_user_allowed_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx ban
ulib_user_denied_access:
Storage ClassINTEGERTEXTTEXTColumnuser_steamid64access_stringaccess_tagExample Value76561198041544289ulx banid
ulib_group_allowed_access:
Storage ClassTEXTTEXTTEXTColumngroup_nameaccess_stringaccess_tagExample Valuevipulx votekick
Old Schema:
ulib_users:
Storage ClassINTEGERTEXTINTEGERTEXTTEXTTEXTTEXTColumnsteamid64steamiduniqueidnamegroupallowdenyExample Value76561198041544289STEAM_0:1:406392801164255280roastchickenoperator"ulx ban"
"ulx kick""ulx maul"
"ulx banid"
ulib_groups:
Storage ClassTEXTTEXTTEXTTEXTColumnnameallowinherit_fromcan_targetExample Valuevip"ulx blind"
"ulx cloak"user!%operator
Megiddo:
Access tags are for specific permissions on a per-argument basis (who you can target, what you can specify, etc). As such, there's no such thing as an access tag for denied accesses.
Thinking about it, since access tags should be relatively rare, it may be worth splitting it into it's own table, but it's six of one, half a dozen of the other to me. It would make a much larger difference with MySQL instead of SQLite.
roastchicken:
--- Quote from: Megiddo on February 10, 2016, 04:14:46 PM ---Access tags are for specific permissions on a per-argument basis (who you can target, what you can specify, etc). As such, there's no such thing as an access tag for denied accesses.
Thinking about it, since access tags should be relatively rare, it may be worth splitting it into it's own table, but it's six of one, half a dozen of the other to me. It would make a much larger difference with MySQL instead of SQLite.
--- End quote ---
Yeah, I wasn't quite sure if access tags would exist for denied accesses so I just threw it in there just in case. I'm not sure if I'll split the access tags off to a separate table because I don't really see any benefit doing so. However you said it would make a larger difference with MySQL... What exactly would be the advantage of putting access tags into their own table?
Megiddo:
With MySQL you can use fixed-width rows to improve lookup performance, but SQLite does not support that: http://www.sqlite.org/different.html
With fixed-width, you'd be wasting space with the mostly unused access tag column. The point is moot with SQLite.
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version