S
SteveB
I need help in setting up a strong foundation for my database. I can pay
consulting fees if it becomes to cumbersome to discuss in these threads. I'm
comfortable setting up many to many relationships through junction tables,
but the business I am trying to model here is more complicated. I'm aware
that it's better to post direct questions, but I need help with a General
Approach.
The purpose of the database is to track projects. A project can start out as
a prospect (we are trying to win it), then progress to a job (we are awarded
the project). It can also skip prospect stage and go right to "job".
A Project can have the following:
ProspectSalesman (tracks the project, puts it through estimating)
JobSalesman (secures the project as a job)
The project needs at least one of these. At most, it can have 1 of each.
They can also be the same person, if the person prospecting it also secures
the deal.
In the prospecting stage, a project will have a BiddersList, which contains a
list of customers who are bidding the project. Right now, I have a
TblProjects, TblCustomers, and a junction table tblBiddersList. I need to
designate 1 customer as the "PrimaryCustomer" and 1 as the "AwardedCustomer".
The remaining customers on the list are simply "Bidders" and need no special
label.
If we win the job, then the project will also have a "JobCustomer" which is
the customer who awarded us with the job.
This is further complicated by the fact that a Customer Account is
represented by a salesperson. However, in the prospecting stage, the
ProspectSalesperson may be working on a project independent of a customer.
In other words, no particular customer has asked us to look at it.
For anyone willing to help, I can e-mail my database. It will be easier for
you to see what I am trying to accomplish.
Thank you,
Steve
consulting fees if it becomes to cumbersome to discuss in these threads. I'm
comfortable setting up many to many relationships through junction tables,
but the business I am trying to model here is more complicated. I'm aware
that it's better to post direct questions, but I need help with a General
Approach.
The purpose of the database is to track projects. A project can start out as
a prospect (we are trying to win it), then progress to a job (we are awarded
the project). It can also skip prospect stage and go right to "job".
A Project can have the following:
ProspectSalesman (tracks the project, puts it through estimating)
JobSalesman (secures the project as a job)
The project needs at least one of these. At most, it can have 1 of each.
They can also be the same person, if the person prospecting it also secures
the deal.
In the prospecting stage, a project will have a BiddersList, which contains a
list of customers who are bidding the project. Right now, I have a
TblProjects, TblCustomers, and a junction table tblBiddersList. I need to
designate 1 customer as the "PrimaryCustomer" and 1 as the "AwardedCustomer".
The remaining customers on the list are simply "Bidders" and need no special
label.
If we win the job, then the project will also have a "JobCustomer" which is
the customer who awarded us with the job.
This is further complicated by the fact that a Customer Account is
represented by a salesperson. However, in the prospecting stage, the
ProspectSalesperson may be working on a project independent of a customer.
In other words, no particular customer has asked us to look at it.
For anyone willing to help, I can e-mail my database. It will be easier for
you to see what I am trying to accomplish.
Thank you,
Steve