On 18-Jun-2003 NIPP, SCOTT V (SBCSI) wrote:
> Hello... I am currently working on a User Account Management
> system. I am actually a Unix SA who is "moonlighting" at work as a MySQL
> DBA and web developer. I am learning a lot and enjoying the work, but I
> am
> severely lacking in database design fundamentals. I have created a
> couple
> very simple databases, but my two newest projects are getting more
> sophisticated. I was hoping for some DB design help with the following
> example...
>
> Let's say that I have users Mary, Joe, Frank, and Dan. I also have
> servers panther, cheetah, jaguar and lion. The data for each account
> that I
> want to maintain is UID, GID, home directory, and default shell.
> In designing a table or tables to handle this example what can I
> make as a primary key? My idea was to have a table named mary, with a
> row
> for each server, and each column would hold the data such as UID, GID,
> etc.
> This would mean that the primary key for each row would simply be the
> server
> name.
> By holding all of the data, including server name, in a single
> table, I am not sure how I would define a primary key. I couldn't use
> the
> user name or server name as there would be duplication. I suppose I
> could
> use a dummy numeric field that is auto-incrementing, but I am not sure
> how
> good an idea this is. I think I have read somewhere that you can
> actually
> use a combination of multiple columns as a primary key or index, but this
> is
> something I am obviously not familiar with.
> One other concern I have is regarding performance. The database
> work I have done so far has been dealing with relatively miniscule
> amounts
> of data. This database table however is going to contain information for
> about 80 servers with somewhere around 300 users per server on average.
> This is quite a large number of rows from my very limited experience. I
> don't want to come up with a poor table design that ends up causing
> problems
> down the line.
>
> Well, that's about all I can think of at the moment. I am sure that
> I will have plenty more questions as this progresses. Thanks again for
> the
> feedback.
>
300 * 80 =24,000 rows --this is _NOT_ a lot.
At first, I'd use just one table:
srvr varchar(32) NOT NULL,
login varchar(32) NOT NULL,
uid smallint unsigned NOT NULL DEFAULT 1000,
gid smallint unsigned NOT NULL DEFAULT 1000,
gecos varchar(128),
sh varchar(32) NOT NULL DEFAULT '/bin/sh',
home varchar(64),
... more fields ...
primary key (login,srvr))
A next refinement would change the 'srvr' field to:
srvr tinyint unsigned
and create a 'server' table:
id tinyint unsigned AUTO_INCREMENT, // last octet of ip ?
name varchar(64) NOT NULL,
primary key (id))
That'll make it easy to rename servers.
The table(s) could be populated very easily with a couple of shell, Perl,
and/or awk scripts.
YP/NIS would come in handy as well.
You'd loop on each distinct server name --request a ypxfer of the
passwd map, then suck it into your table.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
