List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 13 1999 10:47pm
Subject:Re: Table design Question
View as plain text  
Chris Trown wrote:
> 
>      I'm contemplating a new project, but there is a problem blocking me and
> I'm not sure how to tackle it.  I basicly know enough to be dangerous, but I
> have'nt cracked this one.
> 
>      I'm thinking about putting my entire radius users file into mysql.  The
> services we are starting to offer demand a more centralized approach to
> controlling remote access.
> 
>      For those who don't know, the users configuration file contains dialin
> parameters for modem and ISDN dialin users.  Each entry can consist of a
> variable number of attributes.  For example:
> 
> user1   Auth-Type = Sql, Framed-Protocol = PPP, NAS-Port-Type = Async
>         Service-Type = Framed-User,
>         Framed-Protocol = PPP,
>         Framed-IP-Address = 123.234.123.234,
>         Framed-IPX-Network = 123.234.123.234,
>         Framed-IP-Netmask = 255.255.255.255,
>         Framed-Routing = None,
>         Port-Limit = 1,
>         Session-Timeout = 21600,
>         Filter-Id = "filtername",
>         Framed-Compression = Van-Jacobson-TCP-IP,
>         Framed-MTU = 1500
> 
> user2-isdn     Password = "password", Framed-Protocol = PPP, NAS-Port-Type = ISDN
>         Framed-Address = 123.234.123.235,
>         Port-Limit = 2,
>         Framed-Routing = None,
>         Framed-Route = "123.234.123.16/29 123.234.123.17 1",
>         Idle-Timeout = 0,
>         Session-Timeout = 0,
>         Filter-Id = "isdn-filt",
>         Framed-MTU = 1500
> 
>      Not all attributes are always used and the values are'nt always the same.
> 
>      I suppose one way of setting this up would be to have one column per
> possible attribute.  Not practical, since the number of Radius auth attributes
> is large.
> 
>      Another approach would be to just create columns for the attributs I need.
> But that is rather short-sighted and doesn't easily allow for expansion.
> 
>      I could put all the attributes into a blob or varchar, but that sort of
> defeats the purpose of having an SQL db.
> 
>      So does anyone have any ideas as to how I might tackle this?
> 
>      Thanks!
> 
> Chris...
> 

Ok, this is just one of the optition with pluses and minuses:

create table param (id smallint, name char(30));
create table user ( id mediumint, and all the stuff that all users would
have in common)
create table param_values ( param_id smallint, user_id mediumint,
param_val char(10))

param_values would store all of the parameters that are specific to a
certain type of user. To pull up a user configuration:

select param_val, param.name from param, param_val where user_id =
$user_id and param_id = param.id;

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
Table design QuestionChris Trown14 Jul
  • Re: Table design QuestionSasha Pachev14 Jul