Skip site navigation (1) Skip section navigation (2)

Table design for basic user management

From: Thomas Løcke <thomas(dot)granvej6(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Table design for basic user management
Date: 2009-11-06 08:53:21
Message-ID: 1f0fa7ae0911060053j2a377edbsa40240e53b4a1d56@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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/f5255453e

Are 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

Responses

pgsql-novice by date

Next:From: Bob McConnellDate: 2009-11-06 12:46:24
Subject: Re: SQL Server
Previous:From: Brian ModraDate: 2009-11-06 05:10:59
Subject: Re: SQL Server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group