Why using username instead of user id?

Fajar A. Nugraha list at fajar.net
Mon Jan 23 13:45:47 CET 2012

On Mon, Jan 23, 2012 at 7:23 PM, Alan DeKok <aland at deployingradius.com> wrote:
>  One deployment I did mandated user IDs for "cleanliness" of the SQL
> schema.  I added a User-Name table, and used its Id as a foreign key in
> other tables.
>  It had the nice property that if you deleted the user, all of their
> configuration went away.  Including their historical accounting data.
>  It caused problems for billing, but the SQL team thought it was a
> great idea.

... and that's why business requirements should always come first :D

>> It works, and is more "ideal" (especially when you have a super-large
>> number of users), but definitely NOT something I'd recommend to be a
>> generic implementation due to the complexity.
>  It doesn't save much.  The main issue is that *most* users have
> minimal user-specific information.  So using an Id doesn't get you much,
> because it's not really used.  And for accounting, you're doing a row
> insert anyways.  So it doesn't make much difference if column 1 is an Id
> or a User-Name.

Well, to be fair, an index on a varchar(64) (the default on username
table) would always use 64 bytes regardles of the actual bytes used
for username. Changing it to int (userid) would reduce it to 4 bytes,
so you'd save 60 bytes on the index and also tens of bytes on the data
(depending on how long your average username is) at the expense of
additional complexity and/or join. Additional savings can also be
achieved by normalizing attribute column. The savings however will
only be apparent if you also normalize radacct, since it's usually
MUCH larger than rad(group)check/reply. If you're space-limited (e.g.
using mysql cluster), it can mean a lot.

>From db point of view only, normalizing is a "better" approach.
Real-life implementation might have other priorities so normalizing is
not always the best choice.


More information about the Freeradius-Devel mailing list