Database Schema & Design

I need help in designing the schema for a database (defining tables & relations between them). I plan on using (don't laugh) this service to create the database & integrate it into our workflow: [login to view URL] I want to create the tables, forms & reports there myself. I have detailed information on what information needs to be captured and I have broken that information into categories. I need help defining the table and most importantly defining the relationships between them so that they will operate when I create queries! ## Deliverables This company gets contracts for projects and then subcontracts the work to qualified individuals. A client contracts with us for a project & we subcontract with a consultant who performs the work of the project. We define "contract" as the contract between our company and our client and a "subcontract" as the agreement between our company and our consultant (to whom we are subcontracting the project) To avoid confusion, we stick to these definitions. For our company's purposes, a "contract" is synonymous with a "project". CLIENT- ? A client links to one or many projects ? A client has one or many distinct contracts with us ? all basic info on client- clients are companies with many contacts & offices ? each client gets an invoice on a timetable from our company per Project CO-CLIENT ? a company or individual that collaborates with the client on the project. They did not enter into the contract with our company. They are "co-workers" of the client. ? A co-client may have one or many projects ? A co-client may link to one or many clients (but it might be easier to link them via a project) PROJECT ? links to one & only one client ? may link to no, one or multiple co-clients ? links to one or many consultants (but only one at a time) ? links to one & only one contract BUT that contract will be modified & possibly extended & these need to be tracked (a) Note A subcontract is negotiated by our company for our consultant & is unique to that consultant. If that consultant quits the subcontract, then a NEW subcontract is written for the new consultant & may vary in details (mostly accommodations & allowances). (b) Also- each subcontract is often modified over the course of time. This needs to be tracked. (c) Therefor, each project has one subcontract with many modifications. Its very important to somehow easily track the modifications to subcontracts. The details may or may not need to be stored (perhaps just a general text field with some explanation or a link to scanned hard copy) but the modification numbers, dates & ability to know which is the most current is vital ? Important!! A Project is modified over time. Every new expense the project incurs requires a modification to the contract! And often Projects (contract) are extended. Perhaps there should be a separate table for modifications to each project (contract)? ? each project has a many work-products. These work-products are tangible deliverable (reports, presentations, etc) & non-tangible activities (conferences, meetings, etc) ? these work products are on a schedule so each work product should be tied to a date (this way alerts can be sent out to us & consultants reminding of due dates, etc) ? there are lots of details per project to store i.e. start & finish date, contract number, location of work to be done, financials, etc. CONSULTANT - ? links to Project. Can link to one or many projects but only one at a time. ? Links to one subcontract (but that subcontract may have modifications) or if that consultant works on different successive project, s/he may have many subcontracts for separate projects. But a consultant only works on one project at a time. ? Each consultant submits invoices to our company & gets paid by our company ? Each consultant submits tangible work product to our company & our client according to the schedule/calendar ? Each consultant submits expense reports for travel expenses & is reimbursed. We want to automate & organize work flow. We want to input information as 1. Projects are presented to us (input project details & client information) 2. Help us keep track of possible consultants 3. Finalize signed contract 4. Input all project details (time line, work product, consultant details) 5. Have the database help us monitor projects by alreting us to upcoming due dates, etc 6. Have the consultants submit all tangible work product online via secure connection (ideally this will include uploading & storing of pdf, docx, rtf, ppt, etc) 7. Have consultants submit expense reports online (this can be as simple as an upload of an excel template or as complicated as an online form) 8. Have consultants submit invoices online. 9. Have consultants enter extensive post-contract, pre-project-start data such as passport number, emergency contact, etc. We are a small company & as such are not worried about the database being "normalized" to the nth degree. Not worried about complete efficiency. If its easier & more cost effective to build a table per project & re-enter some information instead of having it looked up, thats OK. We are able to provide detailed information on what data will go in the database