Reply To: Try out my first PHP web app!

[__]

He stores JSON in a column to represent a map of permissions. This makes the data “opaque” to the database. For example, if you wanted to find all of the “admin” users, you could not query the database to do it: you’d have to select all of the users, parse the JSON, and then check. You can imagine how impractical this might become if you had hundreds or thousands of users, of if the average permissions map was very long.

Instead, user→permission maps should be stored in their own tables in the DB. This way, the database can search and index them:

create table users(
    id serial primary key,
    --  . . .
);
create table permissions(
    name varchar(100) primary key,
    --  description, etc.
);
create table user_permissions(
    userid bigint unsigned,
    permission varchar(100),
    primary key(userid, permission),
    foreign key(userid) references users(id),
    foreign key(permission) references permissions(name)
);

I usually have a table of groups as well, which can assign several permissions at one go. There’s effectively only one group in this example, but it’d be nice if you could efficiently change that as needed in the future.

He also didn’t index his tables beyond assigning a primary key, though he might address this later in the series. The username, for example, should be indexed because it is so likely to be used in a “where” clause in queries. It should also be unique so you don’t have to rely on the application to make sure usernames aren’t duplicated (doing this in the DB would not only be simpler, it would prevent race conditions).

I also found it odd that he defined a global config, and then wrote a class to access it. It would have been so much better to just put the config array in the class.

…Watching a few more now.