Common Thing You See -- Handling Profile Editing Tabs

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
jack_indigo
Forum Contributor
Posts: 186
Joined: Sun Jun 08, 2008 11:25 pm

Common Thing You See -- Handling Profile Editing Tabs

Post by jack_indigo »

You ever been to one of those websites where you login and they give you a tabbed interface to update your profile sections? Sure, they use AJAX, jQuery, and probably jQuery Forms API if they want to be the most efficient. But what's the most efficient and practical way to write this to the database?

I mean, let's say that the entire profile is 15 columns in a table, made up of 4 section groups (like education, skills, personal details, etc.) and let's say you might have 500,000 rows in that table. Some of those columns might contain some text fields with like 4000 characters inside. Do you just take the entire profile sections as a whole and write that entire record into the table as an update? Or, do you write each section individually as necessary, avoiding other sections that don't need updating? How can one do that and yet not have reams and reams of code to delineate that you only want to update just individual sections and not the entire record?

The way I saw it, the most efficient way was to break off the text fields into a separate table, and put all the rest of the fields in another table. Then, I'll put a dirty bit on the form in the screen such that if a field is updated, the dirty bit becomes 1 and a "Save Draft" button is enabled. If one clicks that button, the dirty bit is cleared (0), the entire record (text fields + other columns) are sent via AJAX to the server and written into the database, and then the Save Draft button is disabled. Next, as you click each section tab and do an update to that field, it automatically clicks the Save Draft button. And if your mouse goes over a menubar like to exit and do something else, the mouseover event is captured, a check is done to see if dirty = 1, and then if so it fires the Save Draft button.

Okay, so that would handle the user interface as cleanly as possible, but how does one update the database? Should I update each section separately as necessary in the database, or update the entire record? The easier thing is to update the entire record, I thought. But then there's the performance hit where you're updating fields that aren't changing perhaps in the other sections. I have no idea how slow that might make the database table.
User avatar
omniuni
Forum Regular
Posts: 738
Joined: Tue Jul 15, 2008 10:50 pm
Location: Carolina, USA

Re: Common Thing You See -- Handling Profile Editing Tabs

Post by omniuni »

I believe this is another area where you should adhere to discreteness of data. Say I need to handle work experience for a lot of people, and each person may have one or more jobs that they have done. Now, I create a table, jobs, and every time the person adds a job, they get a new row:

Code: Select all

===================================================================
UID     |   jobyearstart    |  jobyearend   | jobdescription
===================================================================
249     |       2000        |   2002        |      a fun job
--------------------------------------------------------
829     |       1990        |   1995        |    windows 95
--------------------------------------------------------
249     |       2002        |   2004        |   wrote viruses
====================================================================
In this way, the records are discrete. User 249 does not know that their jobs are in different records, but it minimizes the amount of data that must be written if they update the information.

The principle would apply to settings as well, or multiple fields. If along with a persons UID, you also create a setting ID, you can have each setting stored individually and discretely in the table. The additional rows should be no trouble for the database to sort though, and in fact, it should make it extremely fast to grab values as you need them.... get setting_name from table:settings for user_id:X (just random pseudo-code, I'm still new to writing SQL, and tired.)

If that helps or not, let me know, and I'll try to catch your drift a bit better.
Post Reply