best/commont practice orgainzing users. db for each or id in table??

[John Pastrovick]

I am not sure what the most effective way to organize a db for users
is.
I have 40 users (teachers) and 20 tables (grades, assignments,
students, etc).
Each needs access to its OWN grades, assignemnts, etc.


NOW I am not sure how to orgainize users in the DB. Right now I have
the code for 1 user and need to adapt it to several users.

1. A user id in each table and the same db for all users.?

OR

2. A different db with its own set of tables (same names) for each
user.?


Option 1. would be easier for me because it would only involve adding
an id to a the connection string in the include. Option 2, would
require modifying query strings throughout the code in the
application.

However I heard that ISPs usually limit the number of dbases per Mysql
server (don't know why).
On the other hand the dbase per user approach seems to me more
efficient since the number of records in a table is greatly reduced
and each user has access only to its own db.

What is best/common practice in this situation having in mind that the
number of users is limited to around 40 if that makes any diffrence at
all.


Thanks

[Geoff Berrow]

Re: best/commont practice orgainzing users. db for each or id in table??

I noticed that Message-ID:
<a21852f1.04042 22225.6fb2ca85@ posting.google. com> from John Pastrovick
contained the following:
[color=blue]
>I am not sure what the most effective way to organize a db for users
>is.
>I have 40 users (teachers) and 20 tables (grades, assignments,
>students, etc).
>Each needs access to its OWN grades, assignemnts, etc.
>
>NOW I am not sure how to orgainize users in the DB. Right now I have
>the code for 1 user and need to adapt it to several users.
>
>1. A user id in each table and the same db for all users.?
>
>OR
>
>2. A different db with its own set of tables (same names) for each
>user.?[/color]

If you are not sure it suggests to me that your database may not be
properly normalised (ie to 3rd normal form).

Consider.

One teacher can have many students. So the students table can have a
field containing the teacher id. It is easy then to get a list of
students for a particular teacher. However, if one student can have
more than one teacher you cannot use this method. But proper
normalisation is essential to effectively organise your database, reduce
redundancy etc..

This isn't really on topic here and you need to check a database group
or do some research on normalisation.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[.:Ninja]

Re: best/commont practice orgainzing users. db for each or id in table??

John Pastrovick wrote:
[color=blue]
> I am not sure what the most effective way to organize a db for users
> is.
> I have 40 users (teachers) and 20 tables (grades, assignments,
> students, etc).
> Each needs access to its OWN grades, assignemnts, etc.
>
>
> NOW I am not sure how to orgainize users in the DB. Right now I have
> the code for 1 user and need to adapt it to several users.[/color]

The user table should have an id field (unique, index, whatever). Then this
field must map to a corresponding field in each of the other tables. That
way, you know that a particular entry in for instance the grades table,
belongs to a specific entry in the user table. You can then use a JOIN
statement in SQL to quickly amalgamate all the data into a temporary table
that you can iterate over to list all the entries. This is what the term
"relational " refers to in "relational database".

..:Albe

--

http://www.ninja.up.ac.za

[Chung Leong]

Re: best/commont practice orgainzing users. db for each or id in table??

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:dlfh80hp28 14pp503ncklpkgs uk792tkuk@4ax.c om...[color=blue]
>
> One teacher can have many students. So the students table can have a
> field containing the teacher id. It is easy then to get a list of
> students for a particular teacher. However, if one student can have
> more than one teacher you cannot use this method. But proper
> normalisation is essential to effectively organise your database, reduce
> redundancy etc..[/color]

From a data integrity point of view, yes, the schema is flawed. But from a
administrative point of view, I think it's quite reasonable. If we normalize
the database as you said, then who becomes responsible for consolidated
student information? Clearly you would need someone who oversees all the
students. And getting this person to perform this task could be politically
sticky.

I would go with option 2, since it requires the least amount of code change.
Having separate databases also eliminates the possibility of one teacher
modifying the data of another. I don't see the database limit as an issue,
since such a system should never be hosted on a shared server in the first
place. It would be too easy for students to break in and alter their grades.
All they had to do is get an account at the same ISP.

[Geoff Berrow]

Re: best/commont practice orgainzing users. db for each or id in table??

I noticed that Message-ID: <CtOdnS3G9fquTx TdRVn-hw@comcast.com> from
Chung Leong contained the following:
[color=blue]
>"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
>news:dlfh80hp2 814pp503ncklpkg suk792tkuk@4ax. com...[color=green]
>>
>> One teacher can have many students. So the students table can have a
>> field containing the teacher id. It is easy then to get a list of
>> students for a particular teacher. However, if one student can have
>> more than one teacher you cannot use this method. But proper
>> normalisation is essential to effectively organise your database, reduce
>> redundancy etc..[/color]
>
>From a data integrity point of view, yes, the schema is flawed. But from a
>administrati ve point of view, I think it's quite reasonable. If we normalize
>the database as you said, then who becomes responsible for consolidated
>student information? Clearly you would need someone who oversees all the
>students. And getting this person to perform this task could be politically
>sticky.
>
>I would go with option 2, since it requires the least amount of code change.
>Having separate databases also eliminates the possibility of one teacher
>modifying the data of another.[/color]

Chung, you normally post a lot of good stuff but I think you are
completely wrong here. Look at the subject line. best/common practice

I'm about to go to work in a large community college. It has many
thousands of students and hundreds of lecturers. Avoiding redundancy
for such a database would be a major consideration.

Student information (such as address, telephone number), in particular
needs to be centrally organised since it can change frequently, even for
a relatively small number of students.

If there is a problem with one teacher modifying the data of another
then suitable privileges will have to be built in.

Finally, how is the college going to amalgamate all the data from all
its students if everything is stored in individual unrelated database?
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Chung Leong]

Re: best/commont practice orgainzing users. db for each or id in table??

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:ln5k80dgph 1r3jocgbcv652vr eouo7a8v4@4ax.c om...[color=blue]
>
> Chung, you normally post a lot of good stuff but I think you are
> completely wrong here. Look at the subject line. best/common practice
>
> I'm about to go to work in a large community college. It has many
> thousands of students and hundreds of lecturers. Avoiding redundancy
> for such a database would be a major consideration.[/color]

I'm just offering a second opinion, that's all. There are best practices in
theory, and then there is the all important mandate of meeting real life
requirments. If normalization of the database implies the establishment of a
managerial role that you know no one is going to fill, then maybe
normalization no such a good idea.