On Mon, Jan 23, 2012 at 7:23 PM, Alan DeKok <aland@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.
-- Fajar