- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Any database guys here? I need some help
Posted on 1/25/17 at 10:35 am
Posted on 1/25/17 at 10:35 am
I'm using mysql, and I would prefer to stick with it, but I can change to something else if necessary.
I'm sure this is a problem that has been solved many times over, but I haven't been able to find a good example of the best way to do it.
I'm trying to do some basic project management, and I need to store chains of dependent tasks. As a quick example, I have a root Task A which much be finished to start Tasks B and C. B and C can run concurrently, though both must be finished before Task D can start.
The duration of each task will be estimated ahead of time, so I guess my first question is, is it better to store a start time and end time for a given task, or a start time and a duration? I'm thinking start time and duration.
My next question is, since the start time of only the first Task A can be known, how do I handle the start times of the dependent tasks?
I could brute force it all in the app, so that whenever the start time or duration of a given task is updated, it goes down the line and updates all of the dependent tasks. But I'm sure that would be a huge performance hit and a lot of unnecessary database queries. I think I could use a mysql trigger to do basically the same thing in the database itself, but I've never used those.
I'm thinking my schema will be something like:
TASKS
id, name, start_time, duration
TASK_DEPENDENCIES
task_id, depends_on_task_id, dep_type(FtS, StF, StS, FtF)
It would be great if I could just put a formula in the start_time field that checks all the tasks it depends on, adds their start times plus durations, then inserts the latest result, but I don't think that's possible. Could I use a mysql "view" to do something similar? I've never used those, either.
I'm sure this is a problem that has been solved many times over, but I haven't been able to find a good example of the best way to do it.
I'm trying to do some basic project management, and I need to store chains of dependent tasks. As a quick example, I have a root Task A which much be finished to start Tasks B and C. B and C can run concurrently, though both must be finished before Task D can start.
The duration of each task will be estimated ahead of time, so I guess my first question is, is it better to store a start time and end time for a given task, or a start time and a duration? I'm thinking start time and duration.
My next question is, since the start time of only the first Task A can be known, how do I handle the start times of the dependent tasks?
I could brute force it all in the app, so that whenever the start time or duration of a given task is updated, it goes down the line and updates all of the dependent tasks. But I'm sure that would be a huge performance hit and a lot of unnecessary database queries. I think I could use a mysql trigger to do basically the same thing in the database itself, but I've never used those.
I'm thinking my schema will be something like:
TASKS
id, name, start_time, duration
TASK_DEPENDENCIES
task_id, depends_on_task_id, dep_type(FtS, StF, StS, FtF)
It would be great if I could just put a formula in the start_time field that checks all the tasks it depends on, adds their start times plus durations, then inserts the latest result, but I don't think that's possible. Could I use a mysql "view" to do something similar? I've never used those, either.
3
Posted on 1/25/17 at 10:54 am to Korkstand
You can use views to calculate duration and other derived values. However, have you considered MS Project or similar software (I'm sure there are open source variants)?
Posted on 1/25/17 at 12:11 pm to Brisketeer
quote:I'm developing project management software for a particular industry, so MS Project is a competitor.
have you considered MS Project or similar software
Posted on 1/25/17 at 12:53 pm to Korkstand
Ok, gotcha. I'm not familiar with MySQL, but I would think it has triggers. You could use a trigger to set start dates for dependent tasks, once upstream tasks are completed. Of course, you could also handle it at the application layer.
Posted on 1/25/17 at 1:43 pm to Korkstand
Just make a view for Tasks that returns a derived column basically grabbing the MAX datetime value of start_date + duration of its dependent tasks.
You'll use a DATE_ADD function to add duration to start_date, and just get the MAX value based on a grouping of tasks by depends_on_task_id.
Something along those lines.
You'll use a DATE_ADD function to add duration to start_date, and just get the MAX value based on a grouping of tasks by depends_on_task_id.
Something along those lines.
Posted on 1/25/17 at 7:44 pm to Korkstand
quote:
I'm developing project management software for a particular industry, so MS Project is a competitor.
Best of luck to you.
Posted on 1/26/17 at 10:22 am to Korkstand
Are you developing the app by yourself or with a team? You want your app to have three layers, database, business, and user interface. What you are talking about here would really fit best in your business layer.
What language are you using for the app?
What language are you using for the app?
Posted on 1/26/17 at 10:40 am to PacLSU
quote:By myself.
Are you developing the app by yourself or with a team?
quote:Well, seeing as how I'm writing the business logic myself, I was thinking that if the database could handle it, it would do so much better and faster.
What you are talking about here would really fit best in your business layer.
quote:I'm using Go on the back and Angular2 on the front.
What language are you using for the app?
Posted on 1/26/17 at 11:44 am to Korkstand
quote:
Well, seeing as how I'm writing the business logic myself, I was thinking that if the database could handle it, it would do so much better and faster.
Makes sense. The DB logic will be faster though I don't think you would notice it much unless you are updating thousands of records at a time. Handling business logic in the DB code usually takes longer to develop but that's more personal preference.
I'd go with Baers Foot's suggestion of using views with calculated values.
On your schema, I'd use only the Tasks table and have it relate to itself for the dependent task.
TASKS
id, name, start_time, duration, depends_on_task_id, dep_type
CONSTRAINT SelfKey FOREIGN KEY (depends_on_task_id) REFERENCES TASKS (id)
Use your view with a calculated task_begin_time column to create your list of tasks. For the parent task, task_begin_time = start_time. For dependent tasks, task_begin_time = parent_task.start_time + duration.
Posted on 1/26/17 at 1:01 pm to PacLSU
quote:I can see a given task depending on a tree of dozens of others. I will be incorporating timesheets, and I want the app to "notice" when a task is running over the estimate, and to update the projected timeline on the fly. This doesn't need to happen constantly, only when a user requests the schedule, but this could potentially trigger hundreds of updates at once at just a very small business.
The DB logic will be faster though I don't think you would notice it much unless you are updating thousands of records at a time.
quote:I'm going to try them and see what they can do, at least.
I'd go with Baers Foot's suggestion of using views with calculated values.
quote:
On your schema, I'd use only the Tasks table and have it relate to itself for the dependent task.
Well, these dependency trees can get much, much more complex than the example I gave. Whether a task is "ready to start" can depend on multiple other tasks, equipment/resource/material availability, employee availability, etc., so that's why I made a separate table. I'm trying to solve what appears to be a *very* hard problem: the job shop problem.
Posted on 8/11/17 at 2:34 pm to Korkstand
*** OLD BUMP ***
First off I want to thank everyone that helped the first go round, it is much appreciated.
Now, I am finally getting close to launching a beta (at least I think and hope so).
I am working on the user permissions system... anyone implemented their own permissions before?
At first I was going to just make a permissions table with a bunch of fields for each type of permission, but that got unwieldy very quickly. So I'm scratching that off as a dumb idea... agreed?
So here is my current solution:
I have a permission_types table, which as of now only has 2 fields (id & name) and 4 records (CREATE, READ, UPDATE, DELETE).
Then I have a permission_categories table, which also only has 2 fields (id & name), and the records in this table will correspond to each type of resource the app deals with, which as of now is a dozen or so.
Then I have the user_permissions table, which has 4 fields: user_id, cat_id, type_id, and value (boolean). I have a compound primary key consisting of user_id, cat_id, and type_id, so the database will enforce unique permissions.
Then I guess I have to make each function in the business layer check the permissions it needs to run by checking user.permissions.resource.(c/r/u/d).
Does this sound like a sane way to handle it?
First off I want to thank everyone that helped the first go round, it is much appreciated.
Now, I am finally getting close to launching a beta (at least I think and hope so).
I am working on the user permissions system... anyone implemented their own permissions before?
At first I was going to just make a permissions table with a bunch of fields for each type of permission, but that got unwieldy very quickly. So I'm scratching that off as a dumb idea... agreed?
So here is my current solution:
I have a permission_types table, which as of now only has 2 fields (id & name) and 4 records (CREATE, READ, UPDATE, DELETE).
Then I have a permission_categories table, which also only has 2 fields (id & name), and the records in this table will correspond to each type of resource the app deals with, which as of now is a dozen or so.
Then I have the user_permissions table, which has 4 fields: user_id, cat_id, type_id, and value (boolean). I have a compound primary key consisting of user_id, cat_id, and type_id, so the database will enforce unique permissions.
Then I guess I have to make each function in the business layer check the permissions it needs to run by checking user.permissions.resource.(c/r/u/d).
Does this sound like a sane way to handle it?
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News