Thanks for the great feedback. This is exactly what I was hoping
for. Unfortunately, I think I am still missing some key database
fundamentals to completely understanding some of your suggestions. I guess
where I am still missing something is how I can layout the table(s) to
handle 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.
-----Original Message-----
From: andy law (RI) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 18, 2003 3:33 AM
To: NIPP, SCOTT V (SBCSI)
Subject: RE: Database layout and query questions...
Scott,
Define your details table to have a column that refers to the server.
e.g.
Table definition.....
user_name char(20)
server_name char(60)
shell
etc.
etc.
Then you have everything in one table and you can pull out details as you
need.
Primary key should be on user_name, server_name with a secondary index
defined the other way around (server_name, user_name) although the
efficiencies of that differ between DBMS implementations.
To illustrate, give me all the account details for user 'xyz'
select * from table where user_name = 'xyz'
(you can do this on your design, but it is very inefficient if you want to
query more than one user)
Give me all the accounts on machine 'abc'
select * from table where server_name = 'abc'
(you couldn't do this in any efficient manner on your design)
Give me all the accounts who use bash
select * from table where shell = '/bin/bash'
(you couldn't do this in any efficient manner on your design)
Give me all the users who use different shells on different machines...
select distinct a.* from table a, table b
where a.user_name = b.user_name
and a.shell != b.shell
(OK, that's a bit more complicated for a newbie...)
(you can do this on your design, but it is very inefficient if you want to
query more than one user)
Later,
Andy
-------------
Yada, yada, yada...
The information contained in this e-mail (including any attachments) is
confidential and is intended for the use of the addressee only. The
opinions expressed within this e-mail (including any attachments) are the
opinions of the sender and do not necessarily constitute those of Roslin
Institute (Edinburgh) ("the Institute") unless specifically stated by a
sender who is duly authorised to do so on behalf of the Institute.
> -----Original Message-----
> From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
> Sent: 17 June 2003 21:35
> To: 'David N Murray'
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: Database layout and query questions...
>
>
> The logic in having a table per user is basically as follows...
> Each user would have a table with columns such as UID, GID,
> home directory,
> default shell, date created, etc. Each row would then be an
> entry with all
> of this pertinent information for each server that the user
> has an account
> on. This seems to make the most logical sense to me as it
> seems that it
> would be very organized. Please bear in mind that I am an SA playing
> DBA/Web Developer here. I am guessing that there are
> actually a whole host
> of reasons why this is a bad idea. I am eager to learn, but
> please keep it
> basic as I still a database newbie.
> Additionally, I am going to be developing a similar
> system to use
> for filesystem information on our servers. We have about 85
> servers, and
> most of our servers have well over 25 independent filesystems. The
> structure I described above would be similar for this project
> with each
> server having a table, columns being things like size, mount
> point UID,
> mount point GID, volume group, etc. The rows would then be
> the individual
> filesystems mount points themselves.
> Thanks again for all the good feedback. If anyone can
> recommend a
> good book that could help me out on this I would be most
> appreciative. I
> have some MySQL reference books, but nothing that really goes
> into database
> design. I am planning on taking some database courses at a
> local community
> college starting this fall, but I also need something to help
> me learn and
> limp along in the mean time.
>
> -----Original Message-----
> From: David N Murray [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 10, 2003 3:21 PM
> To: NIPP, SCOTT V (SBCSI)
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: Database layout and query questions...
>
>
> Some questions, that might help you answer yours:
>
> What's the advantage of having a table per user, vs. a
> 'users' table with
> one row per user?
> How many rows do you envision in each user table, and what are their
> columns?
> If you are looking to create a per_user table that just
> contains 'key',
> 'value' pairs for each row, why not just a table with
> 'user','key','row'?
> If you collect stats at the user level, and store these in
> the per_user
> table, how do you summarize? select sum(cpu_time) from joe
> union select
> sum(cpu_time) from alice union select sum(cpu_time) from
> sam... Do you see
> my point?
> How much code do you have to rewrite if you have to change DB
> platforms,
> in the future? SHOW TABLES works nice from MySQL, but that's the only
> place it works (AFAIK). You're moving away from standard SQL with a
> per_user design, since DDL isn't very standard.
> Which rule of normalization are you using to decide to use a separate
> table for each user? (In my experience, most production
> systems struggle
> when you go beyond 3NF.)
> Are you going to use any reporting tools (e.g. Access or
> Crystal) and how
> are they going to fare with per_user tables?
>
> Just some thoughts. Feel free to respond or just digest the questions
> yourself. The answer may jump out at you.
>
> HTH,
> Dave
>
> On Jun 10, NIPP, SCOTT V (SBCSI) scribed:
>
> > I am currently working on developing a User Account Management
> > system. The environment I support currently has about 80
> servers, and a
> > user community of several hundred. I currently have a
> Account Request
> > system that I developed running on one of my webservers and
> this is about
> to
> > roll into production supporting our environment. I am now
> working on a
> > database and scripting that will inventory every existing
> user account on
> > all of the systems. In designing the DB layout I am
> thinking of creating
> a
> > table for each user with all of the passwd file information
> as well as a
> few
> > other tidbits. This will allow us much better account
> management than we
> > have ever had in the past, the past being faxed in request forms.
> > My questions are many, but the immediate questions are
> as follows...
> > First of all, is it a bad idea to structure the database as
> described?
> > Basically, the database will eventually contain hundreds of
> tables, each
> > with maybe 10 or so fields. This is what makes the most sense to me
> > thinking about this. Assuming that creating the database
> as such is not a
> > bad idea, I am now trying to figure out how to query the DB
> to determine
> if
> > a new table needs to be built, for a new user, or an
> existing table needs
> to
> > be updated. Below is roughly what I was planning...
> >
> > <connect to database>
> > open (PASSWD, "/etc/passwd");
> > while (<PASSWD>) {
> > @fields = split(/:/, $_);
> > $user = $field[0];
> > my $tblqry = $dbh->prepare("SHOW TABLES FROM Users LIKE '$user'");
> > $tblqry->execute();
> >
> > Here is where I am getting a little confused... I am
> not sure how I
> > get a return value or data from the 'Show' statement. I
> need at the very
> > least a return value at this point to test against so I
> know whether to
> > update an existing table or create a brand new table altogether.
> > Any help would be most appreciated.
> >
> > Scott Nipp
> > Phone: (214) 858-1289
> > E-mail: [EMAIL PROTECTED]
> > Web: http:\\ldsa.sbcld.sbc.com
> >
> >
>
