Can someone check my database design?

[AliG]

Hi guys,

I am brand new to Access and relational databases in general and am trying to learn how to do everything independently, so forgive me if I struggle with some of the more technical terms or if I miss something that seems really obvious. I have been working on this database for a while now, and I keep running into issues with a variety of things. Before I go any further trying to resolve my other issues, I thought it might be good to see if anyone is willing to check my design and let me know if something should be changed.

This database is for a tech consulting company, and needs to keep track of their Consultants, Vendors, Task Orders, Projects, and Invoices. The tricky part is some of the relationships: some Consultants are also their own Vendors (meaning information like the email address, phone number, and address are the same for both), then there are also some Consultants with different Vendor info but the company still only has one Consultant for a given Vendor, and also there are Vendors that provide multiple Consultants to the company. Because of this, I have separated Email, Phone Number, and Address into their own tables so that the user can assign the same value to a Consultant and a Vendor if needed without repeating data.

The company has Projects that it is working on, and each Project is subdivided into specific Task Orders. Because Consultants often continue contracting for the company once a Task Order or Project is completed and are simply assigned to a new Task Order/Project, I have separated the dynamic information (like the consultant's rate of pay) into an Assignment table so that the company can keep records of old assignments and to avoid having to constantly change Consultant records.

Since they are contractors with the company, Consultants send in invoices every month to bill for their labor and expenses. Each Task Order is allocated a certain amount of funding for labor (based off the rates of the Consultants working on that Task Order) and expenses by the client, and then the Consultants are paid from the funding pool of whichever Task Order they are assigned to. I already know that I will need to run reports which pull up how much is funded total vs how much funding has already been spent on a Consultant, Task Order, or Project basis.

Hopefully I have provided enough information here that someone else who looks at my database can see my design logic. Please, if anyone has suggestions/improvements/questions, let me know! Thank you to anyone who is willing to give me some advice

 

[plog]

You got a mess, that I think all springs from InvoiceT. You've got too many other tables directly linked to InvoiceT.

If a task belongs to a Project and the task has a consultant and a consultant is assigned to a vendor, you need only to store the Task in the InvoiceT and you do not store vendor, project nor consultant in InvoiceT.

My advice is to clear out the Relationship Tool, and add your main table (I don't know what that is, but suspect its ProjectT) and then all the other tables that directly link to it. Repost that and let's start working through this there.

 

[AliG]

Thanks plog, I was starting to suspect I might have muddled the relationships up. I cleared out the relationships window and reorganized my main tables.

So now I have it laid out like this: each Project can have multiple Task Orders, but Task Orders can only have one Project --> each Task Order can have multiple Consultants, and Consultants can have multiple Task Orders over time --> each Vendor can have multiple Consultants, and Consultants could switch Vendors (and have multiple over time) --> each Consultant could have many Assignments, but each Assignment is individual to a Consultant --> for each Assignment, there could be multiple Invoices turned in.

Can I lay out the relationships this way instead? Since they are all linked (even though some are not linked directly), can I still pull all my data into forms? For example, if I want to get the VendorName field from VendorT on a form based off of InvoiceT, will that be possible?

I did not create relationships yet for AddressT/EmailT/PhoneNumberT to avoid creating a mess in the relationships window. Those 3 tables will need to be connected to ProjectT, ConsultantT, and VendorT eventually as they all have the relevant data to store.

Sorry if I missed the point here, relationships have been a real struggle for me so far!:banghead:

 

[plog]

This looks easier to digest, but I still think you've taken too big of a bite. You are trying to relate 8 tables all at once, without thinking through each by itself. Also, I think you invoked the letter of my advice, but not the spirit.

For example, while AssignmentT is only linked to ConsultantT, it still has ProjectID and TaskOrderID fields. You've technically eliminated the relationships in that tool, but not practically--the fields still exists to relate them and they shouldn't. Only 1 path between tables.

From an outsider position, going just by naming, it would seem the Assignment table and ItemOrder tables should be more closely related, if not directly. In your database there are 2 tables between them.

Lastly, your consultant/vendor/taskorder tables are not going to work. If consultants can jump around like you say, then when you assign them to tasks you will need to assign them along with the correct vendor. Effectively what that means is you swap the ConsultantT table with the ConsultantXVendorT table in your relationship view. That table is from where you would link tasks to consultants, not directly in ConsultantT.

Again, bit off too much to chew on, I recommend clearing out all but 3 tables and working upward from there. You've got too many branches on this thing for us to talk about them without getting confused about which branch needs what work.

 

[GinaWhipp]

I'm thinking you need to do some reading first...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html
Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

 

[AliG]

Thanks guys! I think I'm going to take Gina's advice and do some reading before I rethink the design. Like I said, this has been a real struggle for me so maybe I could use some enlightenment before I try to tackle it.

 

[AliG]

Hi guys, sorry for the long absence! I read through the links that Gina provided, and did a little additional reading too. I also played around with an ERD program in the hopes that it might get me to think about my project in a new light.

Here's what I've come up with: I figure the Consultant table is my main table--the whole point of this database is really to keep track of the Consultants, and everything else can be neatly categorized by how it involves them in a conceptual set up (i.e. the company wouldn't even be bidding on projects if there were no consultants that could be sent to work on them). If you're looking at my relationship window, I started at the top and worked my way down and out.

Plog, I took out the unnecessary ID fields in my tables, and also made sure to connect the Assignment through the ConsultantXVendor table as opposed to the Consultant table.

I took a lot of time to really think about how things should be connected in this database, and after doing all that reading I think I have a better grasp on design concepts in general. The really tough part for me has been that most examples are set up on the generic customers/products/orders or even the students/classes ideas, and it's been a struggle to apply some of the concepts to a database that doesn't fit well in either of those molds. Hopefully I've started to figure it out with this redesign.

Thank you so much to both of you for all of your help so far--I really appreciate your patience!

 

[plog]

Without knowing what all the fields are for, it looks good. There are a few fields I question (again, I don't know what the purpose of any of them are--you should really fill in the description column of each field in the design view of your tables):

TOLaborFundingAmount/TOTravelFundingAmount -- these fields ae in both AssignmentT and TaskOrderT. Are these 4 fields unrelated?

CalculatedInvoiceTotal -- you shouldn't store calculated values. Will the value that goes into this field be able to be calculated based on data in other tables? If so, unnecessary. Think you might have a few calculated fields in InvoiceT.

PhoneNumberID/AddressID/EmailID -- think you've gone overboard on normalization. You should have the actual value in ConsultantT/VendorT/ProjectT for these, not links to other tables.

 

[GinaWhipp]

Hmm, I arrived late I see Plog got the question I had...

 

[AliG]

Thanks plog! I'll take some time to fill in descriptions.

The Labor and Travel funding amount fields in TaskOrderT and AssignmentT are separate pieces of data, but they are related--a Task Order is given a certain amount of travel funding and labor funding by the prime contractor which is divided between all of the funded consultants (hence their individual funding amounts in AssignmentT). I was actually thinking that I might be better off with a Funding table linked to both TaskOrderT and AssignmentT, do you think so? I know a lot of the reports I will need to run involve funding, like finding the total and remaining funding per consultant/task order/project at a given time based on how much the consultants have already invoiced.

I remember reading that it wasn't good to store calculated fields, I'll have to go back and trim those out.

For EmailT/AddressT/PhoneNumberT, I separated these into their own tables because for a lot (but not all!) of the Consultants the database is keeping track of, they are also their own Vendors and use the same email/address/phone number for both. If I were to get rid of these tables and simply put fields to store this data in the other tables, I would end up with duplicate data in ConsultantT/VendorT. Is there a better way to make data flexible other than having separate tables?

Without knowing what all the fields are for, it looks good. There are a few fields I question (again, I don't know what the purpose of any of them are--you should really fill in the description column of each field in the design view of your tables):

TOLaborFundingAmount/TOTravelFundingAmount -- these fields ae in both AssignmentT and TaskOrderT. Are these 4 fields unrelated?

CalculatedInvoiceTotal -- you shouldn't store calculated values. Will the value that goes into this field be able to be calculated based on data in other tables? If so, unnecessary. Think you might have a few calculated fields in InvoiceT.

PhoneNumberID/AddressID/EmailID -- think you've gone overboard on normalization. You should have the actual value in ConsultantT/VendorT/ProjectT for these, not links to other tables.

 

[plog]

You've got so much going on, and vaguely named objects, that its hard for me to get my head around what things are for specifically. So, the issues I am pointing out are from a high-level view. I really don't know about how you should capture funding, but it stuck out that you had similar fields in 2 tables. I suggest you try and figure out what works best since you understand the data at a lower level better.

As for Vendors/Consultants, you could make a generic 'Contacts' table to hold all people (Consultants and Vendor Contacts). It would store their contact info (address, email, phone, etc.). Then you could add a 'PrimaryContact' field in Vendors and designate which contact is the primary contact for that vendor.

 

[GinaWhipp]

Agree with Plog... we are trying to get you to focus on tables first but you keep *going to the full mile*. You really need to get the tables right and then move on to the balance.

 

[AliG]

Thanks for all your help guys, I'm going to work on my tables and make sure this is all sorted out from the bottom up. There's no point having the database at all if the foundation is shaky and causing issues, and you've both helped me improve what I had by leaps and bounds already!