I might start off with the following tables:
Task ( Type )
People ( Designer Information )
Assignment ( Join table assigning Deisgner to one or many tasks )
Project --< Task --< Assignment >-- People
The first thing I suggest is acutally get yourself a bunch of 3x5 cards and some highlighters (2 colors should do) and start listing on the card information that is unique to a single particular type of record.
Then on each card list a field that is that records key ID and highlight it and number the card
Start with the most central type of record you can think of (In this case product)
Now look at the remaining cards and see if any data can be tied to this record directly
If so on that other card add the key field of this record and highlight it (in the second color and note the card number it comes from)
By now you should understand parent child relationships this method will help you sort out as well as document the structure of your database.
From what you listed you I can see the following tables for sure
You will have to fill in more detail about what kind of info you want to capture on each table but if you use the card method you will be able to structure your data such that you can form the proper relationships then make use of FM layouts and tools to display and track your data in the appropriate way.
Designers and Developers could all be in a people table as suggested above with a classification but it also may make things easier if you want to record different information about those types of people to put them in separate tables.
wow! this forum is really busy, I thought my thread had been deleted when I couldn't find it on the first page.
mr_vodka, aammondd, really appreciate your input.
Using the card method here is what I came up with. I know it is not complete but I can't seem to pin down certain parts i.e. the #4 card, which where I need to figure out when the two stages of the task are due based on the tasks that are involved. I got the calculation part figured out I am just not sure how the Assignments and the Designers card fits in with the rest. I am not even sure what could be the primary key in #4.
Thank you for your suggestions.
You need a task ID In the product tasks
Then instead of 4 different task fields just 1 and assign it a type
(You can create a script that will make 4 records 1 for each type whenever you create a new product. There are other methods of handling this to it really depends on what process you want to follow)
Then you can make assignments to each task id with product ID and task ID as keys.
You can then place completion dates or other tracking information in the assignments You can also compare Tasks for the same product and the related completion if there is a priority to the tasks. Maybe you dont want to assign a particular task till some other has been completed. So you could place the tasks in a hold status waiting for others to complete.
Designers than can see their open/overdue etc assignments or Grab pending tasks it really depends on how your shop functions.
The designers table doesn't really need product id because it will be related to him through their assignments. You would want developers name(ID) on the product rather than the product ID on the developer the relationship is probably 1 developer to many products (Sorry if I was confusing on that earlier its easier to see this way) if you have many developers per product then you need a product-developers table with both keys in it. Im assuming that Developers are responsible for the spec of the product and designers are responsible for the actual building
Yes you are right, a developer can have many products but each product has only one developer. Each designer can have many tasks related to a product and each product can have many Designers working on it. I am guessing this is why we have an Assignment table between the Designer and the Task.
I am going to read your reply once more when I get home this evening and try to understand it better. Once again thanks for taking the time.