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]
