
SQLite schema boilerplate for user accounts, roles, logins and auth tokens - koistya
https://github.com/membership/membership.db/tree/master/sqlite
======
henrikschroder
I hate to be that guy, but why is something as trivial and inflexible as this
being voted up?

It's the schema for the user accounts and logins for a specific application,
but without the actual application or documentation that would explain what
the idea of each table and column is?

Is this meant as a "Here's how you could model a database for doing this", why
is it lacking any explanations on how to use it, justifications for the
choices, or examples on how the UI flow would look like?

Why is a user's phone number so important that it's part of this, but not a
user's last IP address or time of login?

Super confused here.

------
datashaman
Your table naming convention needs work...

    
    
        User model -> User table
        Role model -> UserRole table
        UserRole model -> UserUserRole table
    

If you consider that User is the module, then your User table should be called
UserUser if you want to be consistent.

But that's silly.

Calling your module Auth would be better. Then the table names don't contain
RepeatedRepeated parts.

Or even better, don't use the module name in the table name. It's pretty clear
what User, Role and UserRole mean just on their own.

And since you're considering using SQLite as the DB, your system will
(hopefully) be small enough not to worry about naming collisions, so why
bother with module scoping the tables at all?

------
datashaman
If you want something that is a lot more descriptive and generic, get your
ideas from this.

[https://schema.org](https://schema.org)

------
cookiemonsta
How is this on the front page?

Surely if you are coding something to use this, you would be able to write the
30-odd lines that these sql statements are...

also table names like "UserUserRole" don't look right to me.

------
UnoriginalGuy
Is there any documentation for this?

For example is "securityStamp" in User.sql designed to store the salt? Or is
it something else entirely? There's security stamps in other frameworks which
are just unique values (e.g. UIDs) which just change when a user's password
changes so you can invalid old cookies without storing the hash within the
cookie.

If there is no salt support at all then this is inappropriate in 2016 as
boilerplate.

------
vhost-
Why the camel cased columns?

~~~
koistya
Good question. This db schema is supposed to be used by web developers. With
camelCase columns it might be easier for developers to query the database
directly from the code (unless they use an ORM, in that case snake_case should
also work well).

~~~
yeukhon
How so? It doesn't really matter. With uppercase you now have to put quote in
SQL, I am not sure other drivers, but in psql you do. Just saying.

------
Thetawaves
Please do not use SQLite for multiuser systems. You will have a bad time.

~~~
gecko
In all seriousness, ever since SQLite got WAL support, it's been fine for
light multiuser systems. I would define "multiuser" as "around ten or fewer
concurrent users", and I think there are some major backup considerations that
go with this, but I don't think it's an inherent nonstarter.

~~~
Thetawaves
WAL still requires a table level write lock.

~~~
batbomb
SQLite uses only file locking.

~~~
rarrrrrr
That was true for a long time, but newer sqlite can do table level locking
when in shared cache mode. Section 2.2 here:
[https://www.sqlite.org/sharedcache.html](https://www.sqlite.org/sharedcache.html)

