Recordset Strategies

What is the best way to think about recordsets?

[cgallent]

I'm having some issues with recordsets. Here's the question:

Is it better to have one recordset with all of the items that you would need in a page? Or, is it better to have multiple recordsets that focus on the specific tasks?

Here's an example. I have a page that both shows records from a timesheet database and at the bottom of the table is a form to update the required fields. I also am calculating the SUM of the total hours for the project through a recordset. Should each of those elements require their own recordset?

Let me know if you need a little more clarification.

Thanks in advance,
Cgallent

[coopster]

Not quite sure where you are going with this, but as a standard, I'd stick with database normalization rather than record-grouping/recordsets based on page content.

Should each of those elements require their own recordset?

I'd say yes.

[cgallent]

Where I'm going with this is that I'm trying to get a better understanding of how to build efficient db pages whe I select different sets of data.

When I build a complex page that uses a database in different ways, should I try and get one "master" recordset that has "everything"? Or, several smaller, more concise recordsets?

Thanks.

[coopster]

I'd go with normalized recordsets. You said:

Here's an example. I have a page that both shows records from a timesheet database and at the bottom of the table is a form to update the required fields. I also am calculating the SUM of the total hours for the project through a recordset. Should each of those elements require their own recordset?

Here is a very simple example of database normalization using your scenario:

Fields (columns) required:
==========================
Employee
Date
Hours_Worked
Project_ID
Description
Total_Hours

If you were to make this a single file (table) or "recordset", it might end up looking something like this:

Employee Date Hours_Worked Project_ID Description Total_Hours 
======== ==== ============ ========== =========== =========== 
Bill.....9/23............3.AAA1234567.WebmasterWW...........3 
Bill.....9/24............1.AAA1234567.WebmasterWW...........4 
Bill.....9/25............5.AAA1234567.WebmasterWW...........9 
Bill.....9/26............3.AAA1234567.WebmasterWW..........12 
Bill.....9/27............3.AAA1234567.WebmasterWW..........15 

It's easy to spot where we should be separating our data here (notice how Project_ID and Description are being duplicated row after row after row...). Whereas, what you really want is to separate the data to stop the redundancy and use programming techniques to get totals based on stored data (read: don't store totals/sums):

Timesheet table:
==========================
Employee
Date
Hours_Worked
Project_ID

Project table:
==========================
Project_ID
Description

Calculated data (not stored, but programmatically displayed upon request):
==========================
Total_Hours

Timesheet Data: 
Employee Date Hours_Worked Project_ID 
======== ==== ============ ========== 
Bill.....9/23............3.AAA1234567 
Bill.....9/24............1.AAA1234567 
Bill.....9/25............5.AAA1234567 
Bill.....9/26............3.AAA1234567 
Bill.....9/27............3.AAA1234567 

Project data: 
Project_ID Description 
========== =========== 
AAA1234567...WebmasterWW 

But, you might say, the Project ID is still being duplicated! Well, that is a requirement when you are using a relational database. Somehow we have to be able to link from the Timesheet data to the Project data in order to get the project Description to display on the screen. That is where the programming comes in to play. It is also how we are going to get our SUM. Here is a sample SQL query showing how we would retrieve our data:

SELECT Employee, Project_ID, Description, SUM(Hours_Worked) 
AS Total_Hours FROM Timesheet, Project WHERE 
Timesheet.Project_ID = Project.Project_ID AND  
Project_ID = 'AAA1234567'; 

Returns:

Employee Project_ID Description Total_Hours 
======== ========== =========== =========== 
Bill.....AAA1234567.WebmasterWW..........15 

And that, my friend is database normalization at it's simplest level. A quick search on Google or other development sites on "database normalization" or "relational database development" should produce some better tutorials.

Regards, coopster

[daisho]

If I understand what your asked your not talking about DB Normalization or format you talked about RecordSets at the PHP level right? ie different calls to mysql_query() on the same page?

If so then I always try to minimize the number of queries per page since each one adds overhead and server roundtrips (ie slower).

If I missed the mark and you were really asking about DB Design and Normalization simply ignore this comment :)

daisho.

[coopster]

Boy, daisho, if you're right, I sure misunderstood the post and wrote a book for nothing! And you know what, now that I read it again, I think that is what cgallent is looking for.

Anybody know a good publisher? ;)

[daisho]

Ya I think so also coopster.

I got a little worried I was way off base when I read your detailed post. Though when we are talking recordsets and SQL the topic of Normalization in my oppinion is *NEVER* a waste or to far off base.

daisho.

[cgallent]

Right on that regarding the recordsets issue.

Thanks though on the normalization topic, if fact that's exactly how I built the db.

Any other thoughts on recordsets?

Cgallent

[coopster]

Well, as daisho stated, there is some overhead to the database calls, but I tend to program for maintainability. Servers are so fast nowadays that you'd be hard-pressed to see a difference between one query to get a recordset versus five. I have dealer locators that run through 50,000+ records performing file joins, complex trigonometric equations AND logical comparisons in milliseconds. Also keep in mind, you can always unset() recordsets once you are finished with them, which will clear memory areas.

Half dozen of one, half-dozen of the other. I think it ultimately comes down to personal preference (but you have opened up one of programmers favorite debates here). Watch this thread grow now ;)

[jatar_k]

It always depends on what is being done with the data.

Why do multiple queries if one will suffice. As I loop through the rows reading them into an array why not just sort them into multiple arrays right away?

Multiple db queries just because its easier is pretty much a waste of resources. One complex query can usually get all of the data you need on any given page.

At some point the query will start hogging process and then you need to either rework the query or split it but every case is unique.

I have cases where the queries can be written as one but the processing power needed is ridiculous. These pages ended up having between 2 and 7 queries each and is much faster.

As I said each case needs to be assessed individually. There are exceptions to every rule but....

less queries == faster

thats the rule ;)