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