DB design question

Requirement: Manager will assign targets to his subordinates and subordinates will report on these targets.

Requirements Elaborated: Manager will create a project and give its start date and end date. Manager will add subordinates in project and also add activities in project from list of activities. Manager will assign targets to his subordinates and select frequency of report. Frequency can be Yearly, Monthly, Quarterly, Weekly. Subordinates will report on these assigned targets.

Manager assign targets like:

• Start Date: 1 jan 2011 (Start date of project)
• End Date: 31 Dec 2011 (End date of project)
• Frequency: Monthly (Frequency of report, other values are yearly, quartely, weekly)

Activities:

• Install OS on machines
• Install SQL Server on server machines

Assigned Targets:

• Install OS on machines: 100

• Install SQL Server on server machines: 10

Locations:

• London
• New York
• Paris

Now there would be 12 reports (one for each month) and subordinates on these locations have to report monthly. Assigned targets are for each month.

I am little confused about the design of all this.

Q1- Should I generate reports for each months and location in advance and save in db? When user will login to system to report he will see 12 reports (because frequency is monthly, if frequency is weekly then 52 reports) and open a report to report his activities.

Q2- Should I just save Assigned targets in one table, Locations in another table, report frequency in third table and when subordinate report on assigned target for specific month and for specific l开发者_运维百科ocation then save this data to some other table and link this data.

Q3- Any other idea of doing this?

Q4- Manager can also change report frequency. How should I handle this becasue if subordinates has reported on targets then how to change all this?

Thanks.

Note: If this question is not suitable for Stackoverflow then please move this (or tell me) to suitable stackexchange site.

Q1: I would store “frequency of reports” as an attribute of Target. Reports would be logged in the database if, when, and as they are generated by the subordinates. Ensuring/enforcing their delivery is outside the scope of a mere database structure.

Q2: What is Location an attribute of: Project, ProjectAttribute, Target, or other? Store it there. You’d probably have a separate table for Locations as well, particularly if there are Location-specific attributes.

Q3: Some questions: Is there a relation between Attributes and Targets? Is there one Manager, or several? If only ever one, then I don’t think you need to model him/her; if several, you then have to determine and structure the relationship between Managers and Subordinates. (Can a Manager be another Manager’s subordinate?) Subordinates are assigned to Projects by Managers, and assigned Targets in those Projects by Managers. Can a Subordinate be assigned (associated) with a Project without having any currently assigned Targets? How about the reverse?

Q4: As per Q1, log reports as they are submitted, and let the Manager chew the subordinate out if they’re not hitting the desired schedule. Question: If the Manager changes the report frequency, do you need to track what the frequency was at any give point in time? (Probably not, but you never know.)

Kashif, you need to have below tables:

1- Projects --- columns: (start date, end date, Report frequency) etc

2- ProjectDetails columns: (ProjectId, LocationId) etc

3- Locations

4- SubOrdinates

5- Activities

5- Tasks --- columns: (ProjectDetailId, SubordinateId, ActivityId) etc

6- Reports -- columns: (ProjectDetailId, SubordinateId, ReportDate, Year, Month, Week, ReportData)

The Reports table will have no data until a subordinate reports to the Manager. When he reports an entry will be made in this table.

You can generate the report list for the users when they login to the system, based on the Projects.ReportFrequency. You will need an intelligent logic here.