structure question 2

[Joan M]

Hi again! I have a doubt: Let's say I have a tasks table:

---------------------
Tasks Table

id : INT (PK)
idProject : INT
idUser : INT
idTaskType : INT
idInvoice : INT
date : DATETIME
start_time : DATETIME
end_time : DATETIME
pricePerHour : FLOAT
notes : LONGTEXT

Then I have a types of tasks table:

---------------------
Tasks types table

id : INT (PK)
name : VARCHAR(45)

Then I have a types of tasks table:

---------------------
Tasks types table

id : INT (PK)
name : VARCHAR(45)

Also a users table

---------------------
Users table

id : INT (PK)
idGroup : INT
name : VARCHAR(45)

And a projects table

---------------------
Projects table

id : INT (PK)
idCustomer : INT
notes : VARCHAR(200)

And a reference prices table

---------------------
Reference prices table

idProject : INT (PK) -> foreign key to Id at projects table
idUser : INT (PK) -> foreign key to Id at users table
idTask : INT (PK) -> foreign key to Id at tasks types table
pricePerHour : FLOAT

My idea is to have specific prices depending on the project, the user and the task to be performed. As you can see there are two pricePerHour fields (one in the "Reference prices table" and one in the "tasks" table). I've found that in some projects prices change during the project... so it would be able to change the prices on the fly... What do you think if my GUI uses the reference price as basis to assign the task price? That way I would be able to change the price of each task if needed (renegotiating...). Is there any better way to do that?

[Richard Deeming]

Based on that design, you can't have an entry in the reference prices table until you have an entry in the tasks table. So you wouldn't be able to use the reference price (that doesn't exist yet) to set the price when you create the task. Wouldn't it make more sense for the reference prices table to link to the task types table?

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Joan M]

WHOA! yes, that's a typo, I've done like this in the MySQL Workbench... I did not wanted to paste all the SQL script here... maybe too much... Knowing you are right and that I had like this in my original design, do you think it can work well? PS: already modified the original post. Thanks Richard!

[Richard Deeming]

It sounds reasonable. We use a slightly more complicated version of something similar in our airport management software. :)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Joan M]

Slightly you say... :laugh: :laugh: :laugh: :laugh: :laugh: :laugh: It's been years since the last time I designed a database and it was at school, so well... I preferred to ask for advice here before... Thank you for checking it! :cool::thumbsup: