Best way to design my tables...

[wxkeep]

Need some help with ideas for my database design.

The database tracks various projects that our company monitors. Each project will have a case that its related to, typically there is only one project per case - but sometimes there can be multiple projects per case. As information comes in - we also have more information about the project than we do the case because the project is assigned to us first and then we either find or create the case. This means that we may have a project in the system for a while that does not have a case. Each project is then bid on by our various bidders. We have a large selection of people that will bid on many of our projects. And since they're bidding - sometimes they place more than one bid.

So - at first I have my tblCase, tblProject, tblBidder, and tblBids to track everything. tblCase has a caseID, tblProject has a projectID, tblBidder has a bidderID, and tblBids has a bidID. And I have set up a relationship of:

tblCase.caseId 1-to-many tblProject.caseID
tblProject.projectID 1-to-many tblBids.projectID
tblBids.bidderID many-to-1 tblBidder.bidderID

to link it all. And it seems to do exactly what I want. But I have bit more information that I'm not sure what to do with. See - tblBids contains date,amount,bidID, and projectID. If a new bid is placed - I know when, by who, and for what. The new information I want to keep track of is in the case of each project - has the bidder been to the property and has the bidder done a full inspection of the property. I could add that as fields to tblBids - but then each bid for a given projectID would have to match on those two fields. I could add those fields to the bidder table - but how do I keep track of each project they've inspected. Likewise with the project table - how do I track which bidders in a single field? I hope I explained this properly.

I'm guessing I'll have to create a new table to track this simple data. But how would I properly link it so that in viewing that table you could easily see that "johndoe" has inspected "project b"?

Any help that anyone can provide would be wonderful!

 

[Remou]

I guess you are right. You need a tblInspection (or such like):

ProjectID ) PK
BidderID )
Date
Comments

I am a little puzzled as to why CaseID is in tblProjects, rather than ProjectID in tblCases. (You will get change for a penny on the above )

 

[wxkeep]

Looks like I'm adding another table. Not a big deal - just seemed like there was going to be a more "streamline" answer. But if not - then there's not - at least it works! Thanks so much for looking at it for me.

As for the caseID and projectID - I put caseID in tblProjects because each project can only have ONE case - but each case might have more than one project. And short of doing some sort of array in the id field - I figured this was the only way to get it done.

 

[wxkeep]

Alright - another thought comes to mind in my brainstorming the development of this project. Most of our projects fall into different categories - not something we really track . . . yet. But a project might be Longterm, need heavy equipment, require computer skills, require instant action, etc. What would be great is if somehow I could identify all the categories that a certain project meets and then I could also identify which categories certain bidders are interested in or able to bid on - so that I could do a query that would show me which of my long list of bidders are capable of completing the project. That way I can print a list of those phone numbers and mass call them to let them know I have a project that they are suited for.

Given the current design of my database - is this possible? And how? THANKS!

 

[Remou]

What leaps (crawls?) to mind is a Keywords table and a ProJectKeywords table, which would develop over time, starting with a list of keywords which could be added to the ProJectKeywords table as the project developed, revealing new requirements. It might also be worth while having a comment on this table to say whether or not the contractor was satisfactory in respect of the requirement, maybe even a field to show that they subcontracted?

 

[wxkeep]

Oooh- good thinking on the comments and subcontracted points. So - if I had these two tables - how would I reference them. So I had tblKeywords and tblProjectKeywords. What would the layouts of these tables be? I envision three fields: the keyword (PK), the bidderID (to show the connection) and comments. And then I guess the same design for the other - and look for matches? Or would it take more tables than that?

 

[Remou]

I sort of envisioned Keyword, ProjectID, Comments, whatever in tblProjectKeywords so you could link back through the project to all the relevant details, including all bidders and sucessful bidder. But does this mean another table tblBidderKeyword? I think it does. At some stage in the future you will be comparing a project with these keywords against bidders with the other keywords to get 'best match', 90% match, whatever ...

 

[wxkeep]

And I'm thinking you might want a master list table of available keywords so that you can use combo boxes to select potential keywords instead of letting users type in their own. Otherwise you might miss out on matching when one user enters "Heavy Equipment" for project and then a bidder is listed as being able to "Run tractors and cranes". Or whatever. YIKES - this is going to be big. Some of those tables are going to get really long over time too...

 

[Remou]

Do you know any friendly librarians in your industry? Librarians often have wonderful lists of keywords. Occasionally these are free.

 

[wxkeep]

Remou - I apologize for the delay in my response. I think I may actually have my database working the way I had envisioned - and I wanted to say Thank You for all of your help - it was paramount to my success!