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
>
>
