Re: database structure question...
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... - don't split data into separate tables by location. Seperate it by the type of information. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
You may want to look into normalization to keep everything organized. However if you want pure query speed, you will have to de-normalize some of your tables to get optimum speed. Have a look here for info on normalization: http://databases.about.com/od/specificproducts/a/normalization.htm HTH --- bruce [EMAIL PROTECTED] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
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] You're *far* better off putting everything in one table and using a field in the table, for example CollegeID, to identify which column you're dealing with. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database structure question...
even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 5:34 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: database structure question... 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] You're *far* better off putting everything in one table and using a field in the table, for example CollegeID, to identify which column you're dealing with. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
RE: database structure question...
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]
Re: database structure question...
bruce wrote: even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce That's right. Databases are made for this sort of thing. If you have a separate table for each location, constructing queries to pull data from a number of them at once will be an absolute nightmare, not to mention what will happen if you have to modify the table structure. For example, what do you do if you want to see all records that were entered yesterday? You run 1000 separate queries! You can bet that this will be slower than if everything was in 1 table. Seriously, put everything in 1 table. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
You have not said what type of information you will be storing in this database. Is it going to be just faculty information? Even if it is just faculty information, you do realize that each school treats departments a bit dfferently. The faculyt maybe under different school, may specialize in certain field(s). What about all those data? Or are you going strictly for First, Last, College type simple scenario? On 7/7/05, Daniel Kasak [EMAIL PROTECTED] wrote: bruce wrote: even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce That's right. Databases are made for this sort of thing. If you have a separate table for each location, constructing queries to pull data from a number of them at once will be an absolute nightmare, not to mention what will happen if you have to modify the table structure. For example, what do you do if you want to see all records that were entered yesterday? You run 1000 separate queries! You can bet that this will be slower than if everything was in 1 table. Seriously, put everything in 1 table. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
