Best approach for basic PHP DB session data storage

Question

I've decided to switch from PHP's native session management based on $_SESSION to use my own, due to many different issues I've had so far with PHP's possibilities, and also because I'm building an API that must be compatible with mobile app requests; so I cannot rely on $_SESSION anyway. I know that there are frameworks as symfony etc. out there for this, but using sth like that for these baby operations would be overkill.

What I'm implementing at the moment is thus according to this logic (source):

The last point I'm wondering is that Redis / the linked article recommends to "Store Session and user info" after a first successful authentication like a login or similar. However, I think that it would be much more beneficial to not do that, and to only store the session identifier, kind of like a VIP card that will permit the client to launch requests in the secured area of the web app.

My though was just that:

• most of the concerned user info lies around in the DB anyway and in unencrypted form; most of it even in a single DB table.
• I think it's generally a bad practice to store the same data in multiple locations of a DB, even if one is stored for "faster" or "facilitated" access in that sense.
• The entire user info (= all data needed for all possible REST requests of the platform, gathered in one JSON) covers around 50 key-value pairs, and some of these values are encrypted, so I'd also need to encrypt the resulting user info, resulting in huge en- / decryption operations upon every request (and page load, as also used for authentication), and an according additional column of sth like VARBINARY(600).
• For a request, I never need more than 10 of those k-v data, and mostly only sth like 2 - 3.

Given the considerations above, I wondered: is it not complete overkill and huge data transfer for nothing, to store the entire user info (sum of all user data I need in all of the REST requests of the platform) along the session identifier to mimic a session? Isn't it better to ONLY store the session identifier in the DB, and query only the needed user info when the actual request comes in (step 4a of the img above) ? Doesn't that make much more sense?

I just feel like wasting so many resources if I store the entire user info along with the session identifier. As that also means that I'd be decrypting and sending a significant amount of payload in vain for different requests.

So what is better practice here, store the user info at authentication (step 1b) or retrieve it when authentication to a request (step 4b) ??

To give you an idea; the resulting tradeoff would need to be made in between:

• decrypting the data and sending much more user info than actually needed by the endpoint, with every request

VS

• doing at least 2 JOINS on PRIMARY KEYs upon every page load / request, in average, and eventually 1-2 smaller decryptions in that query, as well.

In terms of performance, I'm clueless about what's smarter here..

And well another tradeoff is coding convenience, as not storing the whole session data set as such in the DB means one specific user info DB request per REST call.

Answer 1

[This is a cross between "opinion" and "experience". YMMV.]

I have no qualms about doing 40 queries against a database for one web page. So, I would probably send a single id around, not 40 items in a k-v.

One web page needs one connection. The second web page needs a new connection. Information passed from one page to the other must be handled via cookies, the URL, or $_SESSION.

$_SESSION (together with serialization, if needed) is one way to save info between pages. But,... It only works for small sites since the Session array is kept only on the one server.

Use microtime(true) to see how long each database action takes. Most will be under 10ms.

The slowest part of your tasks might be the network between client and server. So try to avoid sending data that is not needed.

(I'm not a fan of REST.)

JSON can be used for passing around k-v info, or even storing it in the database. But don't plan on searching on any of the contents of the k-v. It is easy enough to fetch a row with multiple columns, then do json_encode() to turn it into k-v.

If you might have UTF-8 text, I recommend adding the flag JSON_UNESCAPED_UNICODE.