as of now.. i've heard that there might be a file limit.. but given that i'm using linux, i doubt it.. and if there is a limiit.. i'm sure it's a kernel option that i can tweek...
in my app, i'm not worried about profs transferring between schools... that data's going to be ptreety static, and separate between schools.. but i haven't heard anyone talk to the issue of timing, with regards to doing queries/selects/etc... although, i can imagine the kind of query that might stretch across multiple tables (>10) might get to be painful... on the other hand, if i have all the information in a single table and need to make a change to the table, i'd have to move around/modify/deal with a serious number of records, whereas, if the college data is in separate tables, it would make changes alot easier.... or, i could do a hybrid solution if performing actual queries makes sense.. i could have a 'temp' master collegeTBL that contains all the information, and this table is comprised of the smaller separate collegeTBLS, and i could simply make any changes to the smaller tbls, and rebuild the master table from time to time... hmmm.... -bruce -----Original Message----- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 6:07 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: database structure question... 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]
