4 ms·

I hadn't thought of this approach - It looks a little weird to me but I might be able to get used to it. I don't think it eliminates scanning the table - it eve

• cryptonector 4y ago
  In SQLite3 PRIMARY KEY and UNIQUE constraints beget an (hidden) index, so there should be no table scans. Also, PRIMARY KEY implies NOT NULL for the columns that make it up, except that SQLite3 has a bug in it that causes it to allow NULLs in PK columns (sadpanda.png). > Could you recommend any reading I should do? First of all, I would start with the O'Reilly pocket book on SQL in general. Second, I recommend you spend some time reading through SQLite3's docs and wikis. There's a trove of generally-useful stuff there. Pay special attention to everything to do with the WITHOUT ROWID feature. Row IDs can be a crutch that slows things down. That said, sometimes you really want an INTEGER PRIMARY KEY, and in SQLite3 that functions like a row ID. As for schema design, you should know about normalization. The general advice is to normalize to the max then denormalize till your queries are performant. Much schema design wisdom derives naturally from that. For example, I showed you how to avoid row IDs because they seem unnecessary and they lead to extra duplication. For example, your role_user, if it use row IDs for its references, then queries on it will have to join to the user and role tables to get the names, or, if you end up having both, row IDs and names in it to avoid that, then now you have extra duplication and you'll even need a second set of FOREIGN KEY references in your role_user table to keep those names up to date. I don't really have a good resource for this kind of knowledge. I've absorbed a lot of it by osmosis over many years, and I've developed some of it on my own. You could use reading about EAV (entity-attribute-value) schema designs, too. Many love to hate them. I don't love them myself. But EAV schemas trivially lend themselves to writing graph queries. For example, one system I work with has a notion of relationships and, interestingly, of relationship groups that are interesting for graph queries, and it represents relationships as an EAV table, so one can easily write a recursive SQL query that traverses all paths from one entity on one side of the graph to the another (or all reachable ones) on the other side without having to make reference to the names of the relationship types. I am of the opinion that an RDBMS could provide both, a relational table metaschema, and an EAV equivalent, so that one could write very clear queries against the former, but also very simple and powerful graph queries against the latter. However, I've not seen that anywhere. I want this. BTW, again, when it comes to security-relevant entities, I highly recommend that you a) not allow renames, b) don't allow deletions -- users can be tombstoned deleted/terminated/separated, but do not allow their names to be reused. The reason is that others will tend to write down usernames, group names, etc., and use those for authorization, and then name reuse can cause you grief. You might allow aliases so that users can get different canonical names if they insist on it, but don't allow their earlier names to be reused.