Database Design help

I am popping my cherry finally and asking for help. I am trying to plan my first full database and was hoping some of you experts might be able to lend a hand on my design. Anything you can offer will be helpful so please post. Thank You all in advance and I look forward to seeing how many things I did wrong. I have attached a copy of the design to this post.
Who is Participating?
JobMaterials relates to SericeReports I think not direct to WorkOrder?

Do you need a table similar to JobMaterials for JobEquipment?

Vendors table should not have partid? (i.e. just the vendor detail, one vendor can provide many parts)

What happens if you don't know the location of a contact (e.g. web form contact, email only information)?

Do photos only relate to  Service Reports and Equipment?

looks pretty sound to me - but that's spending a  couple of minutes looking at an ERD regarding a topic I know only in a generic sense.
KryologicAuthor Commented:
Thank you for your response and I understand its hard to discuss something you are unfamiliar with but I appreciate your comments.

Jobmaterials are  related to work orders because materials must be ordered once a work order is created. A service report is filled out once a job is completed.

One vendor can have many parts but a part can have many vendors. Did I do this correctly?

Before creating a job we must know the customers location. A jobsite is where we will be performing the work.

For now pictures only relate to those two tables. I may alter this to equipment and jobs only.
feel you will need just Vendors (.g. name, address, etc) without a part reference
perhaps you need a Suppliers table that has partid + vendorid?

the comment on location wasn't with respect to JobSite (which does need a location)
can you have a contact (with anyone) without knowing a location was my point

JobMaterials refers to ServiceReportID (fk), but there is no indicated relationship
the relationship to WorkOrder is via ServiceReport I think (just check it)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KryologicAuthor Commented:
I am sorry, I missed a few things before posting. Thank you for catching them.

The foreign key "Part ID" should not be under Vendors. The "Suppliers" table that you are recommending I believe i have accomplished with the Join table named vendor Part, correct?

Again, Sorry I did not understand your statement about the Location Table. You are correct I may not always know their address before it becomes a quote or a job. But I am not sure how that would affect the database.

And again, I am a jackass and forgot to adjust the Job Materials table once I moved the relationship to Work Order. Thank you for paying close attention.

I have attached a new file with an updated ERD.
The "Suppliers" table that you are recommending I believe i have accomplished with the Join table named vendor Part, correct?
yes, I believe so

... Location Table. You are correct I may not always know their address before it becomes a quote or a job. But I am not sure how that would affect the database.
cannot be mandatory perhaps? hence not part of an index? - i.e. it will affect you somehow

plus you possibly create a dependency that the location must  exist before creating other records - which may be very annoying (e.g. when trying to capture contacts)

my 'review' is quick and should not be taken as anything but friendly advice - however it looks to me very workable. What happens now is you get to test the reality of it - and detailed change is quite likely - but you know the subject matter far better than I do.

e.g. I don't really understand why trailers (which are equipment) have their own table (you don't need to explain)
KryologicAuthor Commented:
I appreciate your help. I will award points in a few days, I am hoping some other people may comment if the question is still open and hopefully give some people a good starting point or understanding of what an ERD actually is.

PS. I hate not answering a question when asked.

Equipment is placed on top of trailers for long periods of time.The trailers will always have a direct relationship with the equipment. It honestly could be a One to One relationship here but I need to track a history of the equipment on each trailer and be able to know what trailers are open at all times.

Thanks Again for all your help
Scott PletcherSenior DBACommented:
[Btw, for me personally, a listing along with the picture would be extremely helpful, so I could cut-n-paste names rather than having to type.]

First, congrats on taking this step!  It's amazing (and sad) how many people refuse to spend any time at all designing a db.

I've examined just these tables so far:

Location, Customer, Call Log
Jobsite, Quote, Job
Customer Contact, Invoice

C=Comment; R=Reason

C = Jobsite should not have Customer ID
R = It is derivable from Location, which is a required relationship (since this is a logical model -- when you get to the physical model, you may decide to denormalize the Customer ID into this table as well)

C = Invoice should reference Job ID, not Quote ID
R = you don't bill for a quote, you bill for an actual job; the Job can link you back to the relevant Quote(s)

C = Customer Contact makes me very nervous
R = It's more a Location Contact as modeled, but I think it should be a genuine Customer Contact.  What type of info is stored here?  What if a given Customer employee, who was your contact, moves to a new location?  Does your model handle that comfortably?

C = Job table needs split into multiple tables; not sure exactly how many.
R = Can a single Job not cover multiple Quotes?
      Can a single Job not include multiple Invoices? (Material cost billed @ start; some labor costs paid @ 1/3 through; more paid @ 2/3 through the job; etc.)
      Can a single Job not include multiple Work Orders?
      Can a single Job not include multiple Equipment?  Equipment should be tracked at a lower level than Job: the lower entities can be accumulated to derive the total Equipment used for the Job.
The entire Job table and its relationships needs a thorough review.
KryologicAuthor Commented:
First off, Thank you for your lengthy overview that is exactly the kind of information I was looking for.

I agree and have updated the relationship between Customer and Jobsite.

Agreed and updated. A stupid oversight from using copy and paste.

I was going to include a Main Contact/Number in the Customer Table. My thoughts are that if a contact is to change locations wouldn't I be able to just change his Location ID ?

I was questioning the Job Table as well and hopefully when I answer your questions you will be able to provide me with your thoughts.
          - A Job Can Only have 1 Quote
          - A Job can have multiple invoices because we are a rental company
          - A Job Can Have (and will have) multiple Work Orders
          - A Job Can Have (and will have) multiple pieces of equipment

I think I have the 1st two modeled correctly but are you suggesting that I add a join table between Job/Work Order and Job/Equipment? Would this be a many to many relationship even if a single piece of equipment can only be at one job at a time? And a specific work order can only be attached to one job?
Scott PletcherSenior DBACommented:
>> A Job Can Only have 1 Quote <<

Interesting.  So, say a given Customer asks for two Quotes, then decides today to accept both and agrees to the work.  Those always become separate jobs, not one job?  If so, then OK, the Quote ID can be left in Job.

>> - A Job can have multiple invoices because we are a rental company <<

Then the Invoice ID should never reside in Job itself.  If Invoice ID and Job ID are 1-1, then Job ID goes in Invoice; or, if one Invoice can relate to multiple Jobs, an intersection Entity for Job and Invoice.

>> - A Job Can Have (and will have) multiple Work Orders <<
>> - A Job Can Have (and will have) multiple pieces of equipment <<

Similarly, remove Work Order ID and Equipment ID from Job: they belong in different entities.

You need to go through the standard design process ("normalization", thru at least 3rd normal form).  Verify that every attribute ("column") in an entity ("table") is entirely related to: the primary key, the whole pk and nothing but the pk.
KryologicAuthor Commented:
Correct me if I am wrong in this statement.

A child must know its parent but a parent does not need to know its child (In a one to many relationship).

If that is correct I am confident that the attached section is correct, would you agree?

Now on to a topic you have previously tried to question and I am starting to understand is the Quote to Job Table relationship. A Job can only have a single Quote and a Quote can only be assigned to a single Job. My marking in this case would be wrong because this would cause a one to one relationship.

I was once told that you should remove all one to one relationships and many to many relationships from your model. I am having a tough time picturing that here in this case. If I remove the relationship between Quote and Job tables I am not sure how I could look at a Job and reference the quote.

Thanks Again for taking the time to assist.
Scott PletcherSenior DBACommented:
>> you should remove all one-to-one relationships <<

Nope, not possible.  Something in the original msg got crossed up there.

>> remove all many-to-many relationships <<

Absolutely true.  A many-to-many requires that an "intersection" entity be created; that entity will be one-to-many to both of the original entities.

Looks excellent now overall!

Except :-) ...::

Verify that a Job cannot be done w/o a (formal) Quote first, because that is what the model shows: a Job must be based on a Quote.

Very minor quibble:
Customer ID doesn't technically belong on Invoice, since it is ultimately derivable from Job ID ... but you could "cheat" and leave it there since you will almost certainly include it there in the physical model anyway.
Scott PletcherSenior DBACommented:
Sorry, I'll look at more of the tables tomorrow, I simply don't have time today.
KryologicAuthor Commented:
I am not sure I can thank you enough for all your help! Thank you (and if your in NJ anytime soon I will buy you a beer)
rather annoyed with myself for not seeing this:
Then the Invoice ID should never reside in Job itself. :: good catch.

On a related matter does this database need to deal with payments or is that out of scope?

& I'm still of the view that location isn't a necessary attribute of contact, seems a bit awkward.
KryologicAuthor Commented:
You must really want that beer!

Payments are out of the scope (I will probably mark received payment)

The Location is there because a customer can have 10 locations that we do business with. Each Location could have 5 Contacts. How could I get a list of all Contacts at location XYZ without typing in the complete address? And from the stand point of normalization wouldn't I have the address in my database 5 times if I did not have the Location Table?
yes, its the hint of beer that brought me back
(I'm an Aussie, its a national trait to be alert to offers of beer)

I said awkward - not wrong. No problem with the Locations table itself (to avoid address repetition) and associating a contact to a location (when known) isn't bad - although folks move around a lot too. When implementing you just want to ensure you can capture a contact easily and that may be necessary before you get to the point of knowing location. In the context of job management I guess my concern doesn't really apply too much.

back to beer: but, being an Aussie you would have to find good beer, none of that '... Lite' stuff you call beer but which is aerated water with (some) froth
KryologicAuthor Commented:
Well again I appreciate all your help and If I can find a website to buy beer in Australia consider it done! I will make sure they have the best bud light in town :).

Have a goodnight and again I really appreciate you taking the time to work though this with me. Hopefully others will be able to read this and realize why it is so important to plan a DB and not just create mindless data and ruin the integrity of their hard work.
G'day - just wondering if this question could be closed off now?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.