Database Design Advice/Opinions

[suarve]

Hi,

I've been set a project at work to make a prject tracking system. As this forms the basis for larger system I'm making I could do with someone taking a few minutes to check my design allows me to do the following. Part of system (and what the below design is for) is as follows:

- User Logs in
- Creates a Project (or has a project assigned to them)
- Each project would have multiple steps (or to do lists as my boss calls them)
- Each of these steps has sub items
- Users are able to comment on each sub item step
- Project overview page showing most recent activity on a project - date order of comments and to do lists/items added
- Upon commenting, the commenter can select multiple users to be alerted to the new comment



E.g. for a project to build a website

->New Website
- Create Design
---- Build Site
---- Create CSS
- Use JQuery
- SEO
- Testing
etc etc.


Here's the design I came up with:

Now I'm pretty sure I can easily do all the requirements except the last two. I can imagine for the overview page I'd need some quite painful sql, so I may decide to store new actions in a history table so the overvierw page would simply be a case of selecting data from a particular date date range.

For multiple users being altered to a comment, well only way I think of to do this is add yet another table holding just the commentID and user ID.

Doers anyone see anything that jumps out to them as wrong or really really inefficient?

Thanks

 

[Pho]

I know this is slightly OT, but I recently setup Redmine at work which is a web-based project mangement tool that integrates with SVN, GIT etc. It's very good, and saves you having to re-invent the wheel. I think it does everything you've mentioned above, and generates cool looking Gantt/Calendars .

 

[Manlove is my forte]

Would you not have the RDBMS manage the security of the system (i.e. login credentials)

 

[Haircut]

As already said this appears to be re-inventing the wheel, but if you want comments on the model:

• There appears to be no way to have more that one user on a project. I would expect a User_Projects table or similar?
• How are you implementing parent/child lists? Your design doesn't seem to handle this at the minute
• Do lists have a scheduled start and end date?
• Can lists be assigned to people? How do you plan to manage who is working on what if not
• If yes to the above two items you will presumably also need to implement logic to stop a person being assigned to multiple lists at the same time.

 

[suarve]

I know this is slightly OT, but I recently setup Redmine at work which is a web-based project mangement tool that integrates with SVN, GIT etc. It's very good, and saves you having to re-invent the wheel. I think it does everything you've mentioned above, and generates cool looking Gantt/Calendars .

Would prefer something php/asp based, never touched ror ever At the minute we are paying a fair bit for a web based system. Making stuff like this from scratch is good practice for me and something good to put on the old CV

Would you not have the RDBMS manage the security of the system (i.e. login credentials)

That will be the users table. I haven't included all attributes at this stage.

As already said this appears to be re-inventing the wheel, but if you want comments on the model:

• There appears to be no way to have more that one user on a project. I would expect a User_Projects table or similar? I asked about a couple of those things. Currently it's one user to one project.
• How are you implementing parent/child lists? Your design doesn't seem to handle this at the minute. This was a bit I was slightly stuck on. In the end I decided to have A to do list table (E.g. a list of all the list titles) and a child table (todolist_items on the image) that would store the listid and multiple items on the list). Couldn't think of a better way at the mo
• Do lists have a scheduled start and end date? I asked about this and its not needed.
• Can lists be assigned to people? How do you plan to manage who is working on what if not I did think about that and missed a link in the image. I think maybe adding a userid attribute to the todolist)items table would solve that.
• If yes to the above two items you will presumably also need to implement logic to stop a person being assigned to multiple lists at the same time. My boss wants this as simple as possible and is not really needed as its for a small team of graphic designers. I do take your point though.

Thanks for replies people.

 

[Haircut]

For the parent/child relationship I would usually have something like a Parent_ListID on the ToDoLists table.
The top level items are the ones with null as the parent and then you can recurse through to find the children.
Not sure which database your using, but they all seem to have different ways of doing recursive queries - Oracle is probably the easiest if you have access to that.

The point about lists belonging to people is moot if a project can only have one person anyway, you have the ProjectID on the ToDoLists table and you can get the user assigned to that project with a simple query.

 

[suarve]

For the parent/child relationship I would usually have something like a Parent_ListID on the ToDoLists table.
The top level items are the ones with null as the parent and then you can recurse through to find the children.
Not sure which database your using, but they all seem to have different ways of doing recursive queries - Oracle is probably the easiest if you have access to that.

The point about lists belonging to people is moot if a project can only have one person anyway, you have the ProjectID on the ToDoLists table and you can get the user assigned to that project with a simple query.

It'll be mysql that I'm using. I do remember ages ago when I was first learning sql with an employee/manager table and employees managing other employees. Thought it made the sql query unnecessarily confusing, one of the reason I went with two tables - I imagine using a single table my be slightly quicker/more efficient but the system is for 6 or 7 users with 5 of them being on at a single time.

 

[Pho]

Would prefer something php/asp based, never touched ror ever At the minute we are paying a fair bit for a web based system. Making stuff like this from scratch is good practice for me and something good to put on the old CV

Heh fair enough. If you have remote desktop access it's very easy to set-up on Windows, mostly a case of typing in commands from the installation help and Ruby goes off and sets it all up for you. I'd never touched it either before this.

 

[suarve]

Heh fair enough. If you have remote desktop access it's very easy to set-up on Windows, mostly a case of typing in commands from the installation help and Ruby goes off and sets it all up for you. I'd never touched it either before this.

Might give it a go on my own hosting, guess it's always good to try new things