Page is a not externally linkable
coopster - 3:46 pm on Sep 26, 2003 (gmt 0)
Fields (columns) required: If you were to make this a single file (table) or "recordset", it might end up looking something like this: Timesheet table: Project table: Calculated data (not stored, but programmatically displayed upon request): 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
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:
==========================
Employee
Date
Hours_Worked
Project_ID
Description
Total_Hours
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):
==========================
Employee
Date
Hours_Worked
==========================
Description
==========================
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