Forum Moderators: phranque
This system will need to run with the following criteria
php & mysql (more..?, xml..?)
There are 3 levels of users, 1 admin, 2 worker, 3 staff
any level user can submit a task request of different types each with their own form for submission. I would like for the admin(s) to assign the requests to workers and be able to create either a project with that task or assign it as a unique task (not belonging to any project)
I am curious about what is the best approach for the database structure. Meaning, for example, I am thinking I would have a table for projects, tasks, requests, users but what about the specific task types? should I have a table for each type of task?
should I have a table for each type of task?
No, the standard, normalised way of doing this is a task table with a foreign key linking to a task_type table (i.e. 1:Many relationship, each task is of one type, each type can have many tasks).
If you used separate task tables for each type of task you would soon find yourself having to jump through hoops using UNION's to do simple queries like a list of open tasks.
The process of designing a relational db is called "normalisation" - you may want to read up on this before you go any further.
(edited for clarity)
There are 3 levels of users, 1 admin, 2 worker, 3 staff
When building from the ground up, the smartest thing you can do is never assume a set of values is all there is ever going to be.
You're sure of it. The client's sure of it. Everyone agrees. But then a year later, "Hey, I have this worker, she's not really an admin, but I want to let her do some of the admin tasks . . . ."
I have made this mistake a few times before realizing it was a very fundamental mistake. I've had to entirely re-code programs because of it.
Mattur's suggestion applies to this, and is a good one - apply it to your task types as well, always allow them to expand. One might think, "well if they do that, it's future income for me" - which is true. But do you want it to be easy money or hard earned? :-)
Edit due to cross-post:
what if there are different fileds for the task_type table? example tshirts sizes and video formats?
Unions and joins can be a pain, but they're a lot better than spaghetti-coding it together for the short term.
im not sure i understand what you mean by hard code a "type" value?
id ¦ user_id ¦ isAdmin ¦isAccounting ¦ isManagement
Above is an extremely bad way to set up access. Administrators, accountants, and management all have access to different functions which may or may not overlap. Additionally, an accountant may have partial access to an administrative function. Since it's structure is inherent in the database, you would have to add a new field every time a new "type" of employee is required. You'd also have to modify all your programming that accesses this table.
id ¦ user_id ¦ sys_access
1 ¦ 101 ¦ 0
2 ¦ 102 ¦ 1
3 ¦ 103 ¦ 2
access table:
id ¦ access_id ¦ Title
1 ¦ 2 ¦ System Admin
2 ¦ 1 ¦ Worker
3 ¦ 0 ¦ Staff
task_type table
id ¦ task_type_id ¦ title ¦ description
1 ¦ 625 ¦ Accounting ¦ Payroll
2 ¦ 626 ¦ Accounting ¦ Purchase Orders
3 ¦ 628 ¦ Management ¦ Approve Sick Leave
4 ¦ 629 ¦ Management ¦ Approve Time Sheet
5 ¦ 750 ¦ Staff ¦ Busy Work
6 ¦ 752 ¦ Staff ¦ Grunt Work
The records in the above tables can be added or deleted as the system grows without modifying the database. You could display or deny various functions based on the access level, but also could display tasks available based on the task type (think "departments" within a company.) At any time, a new access level can be added to the access table; a new task type can be added to the task types table.
The first is an example of hard-coding (which is probably the wrong term in reference to a database) and the second is an example of a scalable approach.
example where is are the shirt sizes stored?
drooh: generally speaking you want to avoid storing multiple items of data in one field as it again introduces potential problems when querying and joining.
I'm not sure how videos and t-shirts fit into your project tasks database(?) It sounds like this data is "product" info, so should be stored in another bunch of tables for product, product types, product attributes, etc. See this good discussion about Ecommerce Database design [webmasterworld.com].
There's a lot of info on the web about database normalisation and this is the technique you need to grok ;) to arrive at an effective database design.
The more normalised you go the more flexible your application can be (i.e. avoiding application re-writes due to data model changes) but it comes at the cost of more complexity, though this can be mitigated with database "views". How far you go with normalisation requires experience/experimentation.
Learn normalisation. Without it, you're just feeling your way in the dark. HTH.
but what I am specifically asking is this.
Users will submit task requests into the system. These tasks requests will all be of different types. They will share many fields in common such as due date, submitted by, title, description, etc. but they will also have fields unique to their type, such as sizes for shirts, or video formats. I understand to have a table for tasks that has all the fields they share but what about the fields that are unique to that task? Would there be a table called task_type_video and task_type_tshirt ? if not what table would those unique details be stored in? This is where I was wondering if xml would be fitting? (?)
such as sizes for shirts, or video formats.
Well, we're kind of mixing analogies here, at first it sounded like a project manager, now it's sounding like products. It really depends on what you are doing overall.
For example, products and product options: I would never name a product option field "size" or "color". You could add to that list forever. I would do it more like this:
products:
id ¦ prod_id ¦ title
1 ¦ 103 ¦ shirt
2 ¦ 104 ¦ video
3 ¦ 105 ¦ Picture Frame
options:
id ¦ prod_id ¦ option_id ¦ option_name ¦ option_value
1 ¦ 103 ¦ 1234 ¦ Color ¦ Red
2 ¦ 103 ¦ 1235 ¦ Color ¦ Green
3 ¦ 103 ¦ 1236 ¦ Color ¦ Blue
4 ¦ 103 ¦ 1237 ¦ Size ¦ Small
5 ¦ 103 ¦ 1238 ¦ Size ¦ Medium
6 ¦ 103 ¦ 1239 ¦ Size ¦ Large
7 ¦ 104 ¦ 3214 ¦ Length ¦ 90 minute
8 ¦ 104 ¦ 3215 ¦ Length ¦ 120 minute
9 ¦ 105 ¦ 6154 ¦ Size ¦ 5 x 7
10 ¦ 105 ¦ 6155 ¦ Size ¦ 8 x 10
11 ¦ 105 ¦ 6156 ¦ Size ¦ 11 x 14
12 ¦ 105 ¦ 6157 ¦ Frame ¦ Chrome
13 ¦ 105 ¦ 6158 ¦ Frame ¦ Red Acrylic
14 ¦ 105 ¦ 6159 ¦ Frame ¦ Blue Acrylic
12 ¦ 105 ¦ 6160 ¦ Frame ¦ Wood
Note that shirt has two options, size and color, as does Picture Frame. You could add as many options as the item requires. In truth, this approach is not fully "normalized" due to the multiple values that occur under option_name, but is enough to explain the concept. Really, those two columns should be split off into another table, but close enough for this demo (which is what the creators of Windows probably say before each release!)
This is where I was wondering if xml would be fitting?
XML is a markup language. It is used to send and receive data in a format that allows extensibility (i.e., you create your own tags and define what those tags represent.) You can certainly format your page output(s) using XML, or send requests to your programming via XML, but it really has no direct effect on your database structure or how it's accessed. It's a method of transport. :-)
