Determining Whether To Make Separate Tables or Not
Posted: Sun Oct 19, 2008 12:12 am
I'm needing to figure out whether the website might run faster on table reads if I used one table for a certain kind of information, or two tables. If it's a little slow on table writes, that's okay with me and is actually anticipated.
Basically imagine a job search site filled with resumes that are created online. You would have fields for login/registration, profile, address, and then like 5 text fields that build up the bulk of the resume.
So, it's obvious that I would have faster logins if I split off the login/registration info into its own table, and since address is actually rarely shown on resumes except under certain mouseclicks, this should be in a separate table as well. But profile and the 5 text fields for the resume text -- I'm wondering whether to throw those together in one table or split them into two tables. I mean, when someone clicks to see a resume, they need to see all the text at once, so I would have to do a join and then display the content. However, if I throw all those text columns into the same table as the profile columns (which are running about 15 columns so far), I fear it will be slow to pull up a single record. So I'm liked darned if I do and darned if I don't. Perhaps you can suggest from experience which is best?
Not that it matters, but I'm going to be using PostgreSQL 8.2 on this one, hosted on Linux, and with PHP5.
So, what's your opinion? Two separate tables because I've got all those text fields, or one table and throw in the text fields anyway?
Basically imagine a job search site filled with resumes that are created online. You would have fields for login/registration, profile, address, and then like 5 text fields that build up the bulk of the resume.
So, it's obvious that I would have faster logins if I split off the login/registration info into its own table, and since address is actually rarely shown on resumes except under certain mouseclicks, this should be in a separate table as well. But profile and the 5 text fields for the resume text -- I'm wondering whether to throw those together in one table or split them into two tables. I mean, when someone clicks to see a resume, they need to see all the text at once, so I would have to do a join and then display the content. However, if I throw all those text columns into the same table as the profile columns (which are running about 15 columns so far), I fear it will be slow to pull up a single record. So I'm liked darned if I do and darned if I don't. Perhaps you can suggest from experience which is best?
Not that it matters, but I'm going to be using PostgreSQL 8.2 on this one, hosted on Linux, and with PHP5.
So, what's your opinion? Two separate tables because I've got all those text fields, or one table and throw in the text fields anyway?