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?
Determining Whether To Make Separate Tables or Not
Moderator: General Moderators
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Determining Whether To Make Separate Tables or Not
It's usually a good idea to keep data as discrete as possible. Use for login the minimum amount of information, and identify each user with their primary key. This allows you to create as many tables as you need for additional data, and keeps each table discrete so that if you need to modify or add something, you don't endanger more data than you have to if something goes wrong. (2 tables.)
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Determining Whether To Make Separate Tables or Not
Wow. Good point. Didn't think of it from that perspective. Much appreciated. Say, in a few mins. I'll have another post in the theory forum, and if you have an opinion, please do share! Thanks.omniuni wrote:...you don't endanger more data than you have to if something goes wrong. (2 tables.)