Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2008
    Posts
    5

    ERD Help! (my try given)

    Hello , im new here and have been looking around and this place looks like a really good site ! First of all i am a student currently i am in the process of doing a project which will be marked at the end of the year.

    I DID NOT! come here to sit on my a** and just receive answers!

    The project that i have to do is create a windows system for a company that will have to deal with quotes, invoices, a client database, jobs, and hiring out contractors to do those jobs. It also has to show when a contractor is available and when there aren't.

    Before i can get on with the database i have attempted an ERD and well basically i wanted to know if it is correct or not or how it could be improved?

    So far ive got the entities:
    USER, QUOTE, CLIENT, INVOICE, JOB, CONTRACTOR, BOOKED DAY.

    Here's what i have come up with so far :
    http://img.photobucket.com/albums/v6...ject-paint.jpg

    Should the redundant relationships be moved? Because i think that they are needed?

    Any replies would be greatly appreciated.
    Thanks, C¬Rose

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can you explain your relationship between users and quotes as per your diagram?
    Is the booked day an entity or is it an attribute of a job? (I don't know!)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    5
    Well the user will 'send' a quote , (the same with invoice).
    Is this wrong and there should be a line between user and client and the realtionship shoud be sends quote instead of quote/invoice being an entity?

    Regarding the Booked Day (Sorry i did not explain this...)
    My reasoning for this entity is because of the (displaying when a contractor is booked featiured that will be required (stated in first post).
    This feature needs to be displayed as a calendar and i just assumed that it would be easier to program if i had a entity dedicated to booked days so the calendar feature could just pull the data from the booked day table?
    Thanks for the reply,
    C¬Rose

    http://img.photobucket.com/albums/v6...ject-paint.jpg
    Last edited by cobalt-rose; 11-04-08 at 15:59.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I was thinking more from a point of the user-quote relationship appearing to be onene... One user can only send one quote.

    I'll try take another gander tomorrow - sleep calls.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2008
    Posts
    5
    Oh gosh my bad... that is supposed to be a one to many... 'one user can send many quotes'... (it has been updated)
    Last edited by cobalt-rose; 11-04-08 at 19:37.

  6. #6
    Join Date
    Nov 2008
    Posts
    5
    Can anybody help me?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Am I get lost in the phrasing, or should the "booked day" entity be better described as bookings?

    And if so, how does this entity relate to jobs and/or contractors?
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by cobalt-rose
    Any replies would be greatly appreciated.
    Here's my 2c:
    • There is a many to many relationship between job and contractor - this isn't good. Whenever you see a line between two tables with crows feet a both ends then chances are there's something wrong.
    • Your booked day table shows what days a contractor is booked but not where.
    • Should booked days go between job and contractor?
    • This is a matter of taste but are there any different fields required for quote and invoice - if not then could they be combined and just have a type field?
    • At the moment a contractor or client can only have one contact address (stored in the main table), is this correct or would you want a contact details table?
    • Is Cobalt Rose a real name? if so it makes our names in England appear rather dull in comparison.
    Mike

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by mike_bike_kite
    • There is a many to many relationship between job and contractor - this isn't good. Whenever you see a line between two tables with crows feet a both ends then chances are there's something wrong.
    There's nothing wrong with a many to many relationship, it just needs to be broken down in to two 1:m relationships!
    Quote Originally Posted by mike_bike_kite
    • Your booked day table shows what days a contractor is booked but not where.
    Is where an attribute of booked day, not a separate entity?
    Quote Originally Posted by mike_bike_kite
    • Should booked days go between job and contractor?
    That's sort of what I was getting at before
    Quote Originally Posted by mike_bike_kite
    • This is a matter of taste but are there any different fields required for quote and invoice - if not then could they be combined and just have a type field?
    Fair point. However I'd clarify that a quote is an approximation whereas the invoice is the actual amount billed, and therefore will rarely (if ever) equal the same thing.
    Quote Originally Posted by mike_bike_kite
    • At the moment a contractor or client can only have one contact address (stored in the main table), is this correct or would you want a contact details table?
    Can't see the diagram atm (photobucket, pah!) but Mike raises a good point to consider.

    P.S. you know that this forum supports attachments? Much easier (and more convenient for answerer types) than external links to photobucket and the like IMO.
    Quote Originally Posted by mike_bike_kite
    • Is Cobalt Rose a real name? if so it makes our names in England appear rather dull in comparison.
    Wasn't it a Brit that came up with the fantastc name "Fi-fi Trixiebell"?
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    George

    You're too long winded and I lost energy half way through. Can you just précis where you think I'm wrong and I'll point out your mistakes from that

    Mike

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Hehe
    There was no jibe intended, Mr Winky was accidently missed off the end!

    The only important comment is the final one about Ms Fi-fi; care to comment?

    George
    Home | Blog

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The only important comment is the final one about Ms Fi-fi; care to comment
    I assume there's only the one Fi-fi Trixiebell in the UK and oddly enough I met her once - though she was about 8 at the time. I was stopped by her father who was desperately searching for the Streatham Megabowl. They didn't appear to be a happy family at the time (it was around his divorce) so I didn't intrude too much and just showed him to the place.

    Naming kids is always a difficult thing - everyone wants to be a little different but at the same time you want them to be able to develop their own personality which might be quite different to the personality of the name you give them. My 1st lad was called Daniel because everyone in our family is strong willed and often a little "difficult" (obviously with the exception of me ) and it did appear like a lions den at times! The 2nd one was called Miles simply because I loved the character Milo in Catch 22 and I didn't know of anyone at the time by the name. Sadly an annoying children's cartoon character appeared the following year called Milo and then everyone started calling their children by the name. C'est la vie.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Catch 22 <3
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2008
    Posts
    5
    Quote Originally Posted by mike_bike_kite
    [INDENT]Here's my 2c:
    • There is a many to many relationship between job and contractor - this isn't good. Whenever you see a line between two tables with crows feet a both ends then chances are there's something wrong.
      Yer, i plan to get rid of the many to manys with a composite key table

    • Your booked day table shows what days a contractor is booked but not where.
      As previously mentioned... where will be an attribute of booked day. correct?

    • Should booked days go between job and contractor?
      Well i know a table has to go between booked days and contractor so i guess it does make sense to move the booked days there. The only reason for making the booked days seperate was because i thought it would be easier to program---> Reason (The client needs to see what clients are available in a graphical calendar and i added the seperate booked day 'entity' for the sole purpose of the graphical calendar, so it can pull the information straight from the booked day entity table and display it. )

      But from what i can gather your advising i should get rid of the booked day table and name the joint table between contractor and job booked day/bookings
      Shown in attach ERD tk2.

    • This is a matter of taste but are there any different fields required for quote and invoice - if not then could they be combined and just have a type field?

    • At the moment a contractor or client can only have one contact address (stored in the main table), is this correct or would you want a contact details table?
      Yes i would like a contact details table...but i just assumed i would add that when i do the normalisation? Should i make contact details an entity then also? How would i represent it?

    • Is Cobalt Rose a real name? if so it makes our names in England appear rather dull in comparison.

    lol no it is not my real name... wish it was though haha!... it is just my internet/online poker ID
    p.s im from england too

    ERD of Project-paint = Old ERD
    ERD tk2 = Your revised ERD
    Attached Thumbnails Attached Thumbnails ERD of Project-paint.jpg   ERD tk2.jpg  
    Last edited by cobalt-rose; 11-12-08 at 10:13.

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by cobalt-rose
    Well i know a table has to go between booked days and contractor so i guess it does make sense to move the booked days there. The only reason for making the booked days seperate was because i thought it would be easier to program
    The booked_day table should be kept and go between job and contractor as in your 2nd diagram. Why can't the booked_day table still be used for the calendar?

    Quote Originally Posted by cobalt-rose
    Should i make contact details an entity then also?
    You could have an address table to hold multiple addresses for both clients and contractors or even users. This would make coding the end application easier but does create issues in that you need a common key between all 3 of these entities. This key will then be used in the new address table. For a college project I'd recommend just storing one address and then putting this down as a future improvement.

    Quote Originally Posted by cobalt-rose
    no it is not my real name
    I'm heart broken

    PS You could try experimenting with Access to build your ER diagrams, it produces better diagrams that are easier to change (and have crows feet that line up). I personally just use pencils and old bits of paper so I can't talk.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •