I would strongly recommend creating one table, with a column that stores the college_ID for each faculty member, and a separate table to correlate college name and college_id. For example...

Create table faculty (
        last_name varchar(50),
        first_name varchar(50),
        college_id int,
        primary key (last_name, first_name),
        key c_id (college_id)
);

Create table colleges (
        college_name varchar(50),
        college_id int,
        primary key (college_id)
);

This sort of structure will allow you to easily and quickly retrieve all faculty for a given college (select last_name, first_name from faculty where college_id="$id"). Also, if a faculty member were to be transferred to another college w/in your system, it is easy to update (update faculty set college_id="$new_college" where last_name="Smith" and first_name="John"). Or, to find what college a given faculty member is at, (select college_id from faculty where last_name="Smith" and first_name="John"). Finding a faculty member from ~1,000 tables would be very, very painful, not to mention slow.

Another reason not to store each college in its own table is that on many file systems, there is a limit to the number of files allowed within one directory, regardless of how small the files are. I believe that on most linux's, it is in the tens of thousands. Not likely to be reached, but if your application grew to encompass tens of thousands of colleges, you would eventually run out of room. (See http://answers.google.com/answers/threadview?id=122241 for an explanation.)


~ Devananda



bruce wrote:

hi...

i'm considering an app where i'm going to parse a lot of colleges (~1000)
faculty information. would it be better to have all the faculty information
in one large table or would it be better/faster to essentially place each
college in it's own separate table, and reference each table by a
college_ID, that's unique and assigned to each college, and maintained in a
master_collegeTBL...

thoughts/comments/etc....

i'm leaning towards the side that keeps each college information separate,
although this means that i essentially have to deal with 1000s of
tables/files...

-bruce
[EMAIL PROTECTED]




------------------------------------------------------------------------



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to