Hey all,
I'm in the process of building a web-application, where some basic user management is necessary. This is not the first time I've done this, but those I've made earlier have all been for internal use (LAN access only, very limited amount of users). This is going to be a public application, so naturally I'm a bit more concerned about performance, security and scalability. The application will open up with ~10.000 users, each doing 1-2 logins per day (0800-1700 weekdays only).
A login basically consists of a few simple steps:
*A username/password lookup.
*Decide users permissions based on her 'group' settings.
*Log stuff like last login and visits.
For some parts of the application, further information is necessary. This will be fetched from one or more 'user_data' tables only when necessary.
I haven't decided on the hash algorithm for passwords yet, which explains the rather large varchar column for the 'user' table.
Outside of the initial 10.000 users, we expect a limited amount of new users registering, probably no more than 5 per weekday.
I've more or less always used SQLite for our internal web applications, because it is simple, easy and low-maintenance. But for this application, I'd rather go with something more "solid", and because we've been running a bunch of internal Wikis on PostgreSQL without a hitch for years, I decided to stay with that.
I've come up with an initial design for this database:
http://pastebin.com/f5255453eAre there any glaring problems with this design?
As far as I can see, PostgreSQL handles indexes automatically on primary and unique columns. Is this assumption correct?
Also, will I gain anything from using functions to read/write data, or is it mostly a matter of convenience and simplified SQL?
The application will be done in PHP using PDO_PGSQL. We're running PostgreSQL 8.38, and plan on sticking with that for a while yet.
As you might have guessed from this post, and from my design, I'm no database expert. The task of solving this has fallen on me, not because I'm good at it, but because I'm the best of the worst here at our offices. Any and all advice is very much appreciated. Currently I'm glued to the PostgreSQL manual, trying to suck up as much knowledge as possible. But there's a lot of words in there, and sometimes a few pointers from experienced users is just what the doctor ordered. :o)
Regards,
Thomas