Solved

Database Design help

Posted on 2013-05-14
20
280 Views
Last Modified: 2014-05-13
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.
Test--1-.png
0
Comment
Question by:Kryologic
  • 8
  • 6
  • 4
20 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 334 total points
Comment Utility
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.
0
 

Author Comment

by:Kryologic
Comment Utility
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.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 334 total points
Comment Utility
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)
0
 

Author Comment

by:Kryologic
Comment Utility
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.
Test--2-.png
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 334 total points
Comment Utility
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)
0
 

Author Comment

by:Kryologic
Comment Utility
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
[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.
0
 

Author Comment

by:Kryologic
Comment Utility
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?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
Comment Utility
>> 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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Kryologic
Comment Utility
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.
Customer.png
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
Comment Utility
>> 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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Sorry, I'll look at more of the tables tomorrow, I simply don't have time today.
0
 

Author Comment

by:Kryologic
Comment Utility
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)
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 334 total points
Comment Utility
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.
0
 

Author Comment

by:Kryologic
Comment Utility
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
0
 

Author Comment

by:Kryologic
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
G'day - just wondering if this question could be closed off now?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now