Schema efficiency check requested 1

[toyt78]

I just created an Access 2000 Database schema and would like to know if it looks efficient and need any advise that you can give me on this.

Here is how I have my tables now:
Project Table

projectID - Primary Key
requesterID - Foreign Key
managementID - Foreign Key
priority
projectInfo

Requester Table

requesterID - Primary Key
requesterFirstName
requesterLastName
requesterPhone
requesterOffice

Management Table

managementID - Primary Key
managementFirstName
managementLastName
managementInfo
managementPhone

The Front end GUI is a Web Form that asks for Requester information (just the first and last name to start) then leads the user to another page to enter the rest of Requester info and the Management information and the Project information.
If the requester first and last name is already in the database then he will have all his requester info already showing on the next page but will have to enter new management info and project info because that always changes.

Please advise because everything works but was hoping to find out if I did this schema correctly and efficient?

 

[lespaul]

Can a manager be a requestor? Why have two tables with almost identical structure? Can these tables be combined with an additional field flagging managers? Have you read The Fundamentals of Relational Database Design?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[toyt78]

Thanks.

Manager can not be a requester. From what you are saying I should combine the management and requester table so that is what I will do to make this more efficient.

I did check the Fundamentals of Relational Database Design and I thought I was on the right track and was hoping to make sure.

 

[lespaul]

No problems, I ask everyone if they've read that! I can't really say if what you have is correct or not, I was just commenting on what you presented. You posted details about the GUI and input flow, but that doesn't really effect the data structure. In order to help you check your data we really need to know more about what you are storing information about.

For instance, I would assume that there's a Request table involved somewhere? You are tracking request for projects that are submitted by requesters that have to be approved by managers. Why are you tracking them? What information are you storing about the projects, the requests, the requesters and the managers? Do you need to assign people to complete the projects?

So, if you want some more assistance, we'll be glad to help critique your design, but we'll need a lot more details about what your doing and not so much about how you envision gathering the information.

HTH

leslie

 

[toyt78]

Thanks!

Here is some info that I hope you have time to answer:

For instance, I would assume that there's a Request table involved somewhere?
No, just the Requester table for the person actually inputing the information.

"You are tracking request for projects that are submitted by requesters that have to be approved by managers. Why are you tracking them?"
Tracking the projects so each project can be prioritized from 1 to 3 for what is the most important project with 1 as the most important.

What information are you storing about the projects, the requests, the requesters and the managers?
Storing the title of project, priority number,history of project and the actual project description. The Requester table is the info about the Secretary who is actually inputting the Project info and Manager info for the Management. The Management table is the Manager who is assigned to manage and be responsible for the Project.

Do you need to assign people to complete the projects?
No, we will not have people assigned to each project.


A Secretary from each office enters the info for a manager and she will enter Projects for different managers so that is why I separated the Requester and Management tables.
For example Sally Jones (Secretary) can enter a Project for Joe Smith (Manager) and then enter another Project for Rich Johnson (Manager).


Thanks, if you can answer or assist on any of these.
I know this has to be efficient for adding and updating the database and creating quieries and reports.

 

[johnherman]

My only "concern" is that the Project table is tied to one requestor and one manager. If this is the correct business rule, i.e. can't have two requestors or two managers, then the design looks good to me.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[toyt78]

Thanks,

In some rare cases I will have two managers for the same project. That is the part I am not sure how to handle if it happens? Can you advise what change I need to do to the schema to handle that??

 

[lespaul]

you remove managerID from the project table and create a new table ProjectManagers:

ProjectManagaers
ProjectID
ManagerID

and create a composite PK using both fields (highlight both fields in the table design and press the Key button).

HTH

leslie

 

[toyt78]

Thanks,

Currently I have it set up like this but will change it as you suggested.:

managementID - Primary Key
managementFirstName
managementLastName
managementInfo
managementPhone
managementFirstName2
managementLastName2
managementInfo2
managementPhone2

Will this way I have it now also work or is there is an issue with the queries and effiecieny?

 

[lespaul]

you really don't want to set it up that way, eventually there will be 3 managers on a project and you're going to have to add all those fields AGAIN! You will be better off doing as I suggested. If a third manager is added in the future with the design I suggested, all you would have to do is add another record with the ProjectID and another managerID.

The Fundamentals document explains the reasoning for this (I think, it's been a while since I read it!!).

HTH

Leslie

 

[toyt78]

Thanks for all your help on this!