structure question
-
Hi all, I'm thinking on making a small application to keep track of my working hours, billable or not... I'd install it in my NAS to be able to access it from everywhere. The database will be a MySQL database. I'll do the programming in PHP (don't know any other thing for web). I've been far away from databases for a long time and wanted to make a question about the right data structure... I plan to have:
------------------
Table 1: CUSTOMERS(PK) ID : autonumeric
Name : string
Address : string------------------
Table 2: PROJECTS (1 customer can have N projects)(PK) ID : autonumeric
Customer ID : number
Description : string------------------
Table 3: TASKS_IN_PROJECT (1 project will have N tasks).(PK) ID : autonumeric
Project ID : number
Person ID : number
Description : string
Date : timeDate
Start time : time
End time : time
Price per hour : number------------------
Table 4: TASKS (N Tasks_In_Table will have 1 task).(PK) ID : autonumeric
Description : string------------------
Table 5: PEOPLE (N Tasks_In_Project will have 1 person).(PK) ID : autonumeric
Person name : string------------------
Table 6: EXPENSES_IN_PROJECT (1 project will have N expenses).(PK) ID : autonumeric
Project ID : number
Note : string
Date : timeDate
Cost : number------------------
Table 7: EXPENSES (N Expenses_In_Project will have 1 Expense).(PK) ID : autonumeric
Description : stringMy idea is having this database structure and then make a small PHP web page that would allow me to introduce customers, projects assigned to customers, tasks and persons for each project (those should be available from a combobox selecting from a list of available tasks (tasks table) and people (people table)), and also I would like to be able to add expenses to the project too in the same way... those expenses should be selected from a list coming from the Expenses table... Do you think this is a good structure? Have I done something terrible? My objectives are: 1. storing all the worked hours. 2. being able to get some reports (total hours in a project, lists of tasks done...) Thank you very much for your time and advice!
-
Hi all, I'm thinking on making a small application to keep track of my working hours, billable or not... I'd install it in my NAS to be able to access it from everywhere. The database will be a MySQL database. I'll do the programming in PHP (don't know any other thing for web). I've been far away from databases for a long time and wanted to make a question about the right data structure... I plan to have:
------------------
Table 1: CUSTOMERS(PK) ID : autonumeric
Name : string
Address : string------------------
Table 2: PROJECTS (1 customer can have N projects)(PK) ID : autonumeric
Customer ID : number
Description : string------------------
Table 3: TASKS_IN_PROJECT (1 project will have N tasks).(PK) ID : autonumeric
Project ID : number
Person ID : number
Description : string
Date : timeDate
Start time : time
End time : time
Price per hour : number------------------
Table 4: TASKS (N Tasks_In_Table will have 1 task).(PK) ID : autonumeric
Description : string------------------
Table 5: PEOPLE (N Tasks_In_Project will have 1 person).(PK) ID : autonumeric
Person name : string------------------
Table 6: EXPENSES_IN_PROJECT (1 project will have N expenses).(PK) ID : autonumeric
Project ID : number
Note : string
Date : timeDate
Cost : number------------------
Table 7: EXPENSES (N Expenses_In_Project will have 1 Expense).(PK) ID : autonumeric
Description : stringMy idea is having this database structure and then make a small PHP web page that would allow me to introduce customers, projects assigned to customers, tasks and persons for each project (those should be available from a combobox selecting from a list of available tasks (tasks table) and people (people table)), and also I would like to be able to add expenses to the project too in the same way... those expenses should be selected from a list coming from the Expenses table... Do you think this is a good structure? Have I done something terrible? My objectives are: 1. storing all the worked hours. 2. being able to get some reports (total hours in a project, lists of tasks done...) Thank you very much for your time and advice!
Joan M wrote:
Have I done something terrible?
No, it's not VB6 nor JavaScript.
Joan M wrote:
Do you think this is a good structure?
Decent :) Just a few questions;
Joan M wrote:
(N Tasks_In_Table will have 1 task)
I don't see that table's structure?
Joan M wrote:
Price per hour : number
Are you going to lookup a price and enter it manually for every task? How about a table with base-tasks and pricing to choose from? Perhaps with an additional column for discounts or extra? Also, you use autonumbers for your keys; That means you can enter a customer twice, without problems. I'd prefer a unique constraint on the autonumber, and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project. It'd also mean that a project would require a unique constraint on its autonumber for quick reference, but also a decent PK; the combination of the username and the projectname. --edit Free tip; if you're unsure about a structure, populate your example with mock-data and try some queries :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Joan M wrote:
Have I done something terrible?
No, it's not VB6 nor JavaScript.
Joan M wrote:
Do you think this is a good structure?
Decent :) Just a few questions;
Joan M wrote:
(N Tasks_In_Table will have 1 task)
I don't see that table's structure?
Joan M wrote:
Price per hour : number
Are you going to lookup a price and enter it manually for every task? How about a table with base-tasks and pricing to choose from? Perhaps with an additional column for discounts or extra? Also, you use autonumbers for your keys; That means you can enter a customer twice, without problems. I'd prefer a unique constraint on the autonumber, and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project. It'd also mean that a project would require a unique constraint on its autonumber for quick reference, but also a decent PK; the combination of the username and the projectname. --edit Free tip; if you're unsure about a structure, populate your example with mock-data and try some queries :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Quote:
and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project.
It's nice until you end up with two customers with the same name! :laugh: In my case I also can't use part of the address as I have a Father/Son scenario with the same name living at the same address (besides, people do move house occasionally). So we have autonumbers - the onboarding process does the necessary checks for duplication
-
Quote:
and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project.
It's nice until you end up with two customers with the same name! :laugh: In my case I also can't use part of the address as I have a Father/Son scenario with the same name living at the same address (besides, people do move house occasionally). So we have autonumbers - the onboarding process does the necessary checks for duplication
CHill60 wrote:
It's nice until you end up with two customers with the same name! :laugh:
You, like the computer, and the cashier, need to be able to distinguish between the two, even in a listview. Wouldn't want to bill the father for the son's purchases :)
CHill60 wrote:
So we have autonumbers - the onboarding process does the necessary checks for duplication
:laugh:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.