Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2008
    Posts
    12

    Entity Relationship question

    First time posting here.
    I've been trying to design entity relationships for a database but I am getting a little confused on how to go about relating a few tables.


    A project is done for one owner company, an owner company can have multiple projects.
    A project can have many consultant companies, A consultant company can do many projects. But a project can have only one prime consultant company.

    Consultant Companies can be represented by multiple contacts (individuals). A contact can only represent one company
    A Project can have multiple Consultant contacts But there can be only one prime Consultant contact for each Project.

    Owner Companies can be represented by multiple contacts (individuals). A contact can only represent one company
    A Project can have multiple Owner contacts But there can be only one prime Owner contact for each Project.


    With
    +====< being (1:m)
    and
    >====< being (m:m)

    These are the Relationships I’ve drawn out:

    Owner company +=====< Project >=====< Consultant company

    Owner company +=====< Owner Contacts

    Consultant company +=====< Consultant Contacts

    Owner contact >=====< Project >=====< Consultant contact

    My concern is that the relationships haven’t been drawn properly and that the Prime contacts wont be detected. Could you please verify if I am on the right path or even propose a better way of doing it. Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you are on the right path

    first thing to consider: a relationship needs a name

    you're missing the relationship from project to prime consultant contact

    you already have one (the m:m one between project and consultant contact) but you need another one (a 1:m one)

    hence they need to be named in order to distinguish them

    second thing to consider: contacts are people, and you might want to haev a supertype/subtype structure for people and the different types of contacts that they can be

    if an owner contact can also be a consulting contact on a separate project, your current scenario would have you entering the same person in two places

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2008
    Posts
    12
    you're missing the relationship from project to prime consultant contact
    you already have one (the m:m one between project and consultant contact) but you need another one (a 1:m one)
    Since the project can only have one Prime consultant company and A consultant company (primary or regular) can only have one Prime consultant contact do I still have to relate the project directly to a Prime consultant contact?

    contacts are people, and you might want to haev a supertype/subtype structure for people and the different types of contacts that they can be
    If you are mean hiararchies by supertype/subtype then I'd say the contact information doesnt go into that much detail. Just the basics and then if prime contact or not.

    if an owner contact can also be a consulting contact on a separate project, your current scenario would have you entering the same person in two places
    I initially wanted to have one contacts table that would differentiate if it was a Owner or Consutant contact. Will this be problematic considering Project and Consultant Company has a M:M relationship where as Project and Owner Company have a 1:M relationship?


    Thank you for the quick reply.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Heminem
    Since the project can only have one Prime consultant company and A consultant company (primary or regular) can only have one Prime consultant contact do I still have to relate the project directly to a Prime consultant contact?
    you do not, provided that the same person is the prime contact on all projects

    which i see as unlikely

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2008
    Posts
    12
    Thanks Again :-)

  6. #6
    Join Date
    Jul 2008
    Posts
    12
    So I went and talked with the stakeholders to clarfiy and obviously you were right.

    But I still remain a little confused.
    I have updated and reorganized the relationships.
    With
    +====< being (1:m)
    and
    >====< being (m:m)

    Owner Relations
    A project is done for one Owner Company; an owner company can have multiple projects.
    Owner company +=====< Project

    Owner Companies can be represented by multiple contacts (individuals). A contact can only represent one company.
    Owner company +=====< Owner Contacts

    A Project can have multiple Owner contacts and an individual owner contact can be related to multiple projects.
    Owner contact >=====< Project

    I am stuck HERE
    A Project can have only one Owner contact as the Prime Owner contact. An owner contact can be the prime owner contact for multiple projects.
    How do I relate the owner contact, the project and the prime owner contact?

    Consultant Relations
    A project can have many consultant companies, A consultant company can do many projects.
    Project >=====< Consultant company

    Consultant Companies can be represented by multiple contacts (individuals). A contact can only represent one company
    Consultant company +=====< Consultant Contacts

    I am stuck HERE
    A Project can have only one Consultant Company as the Prime Consultant Company. A Consultant Company can be the Prime Consultant Company for multiple projects.
    How do I relate the Consultant Company, the project and the Prime Consultant Company?

    I am stuck HERE as well
    A Project can have only one client contact as the Prime client contact. A client contact can be the prime client contact for multiple projects.
    How do I relate the client contact, the project and the prime client contact?

    The relationships will typically be the exact same I believe.
    Last edited by Heminem; 07-18-08 at 17:31.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    data modelling is really simple if you have sample data

    getting sample data is tedious but simple

    do you have any sample data for this project?

    because if it's a homework assignment, you won't, but if it's a real-world project, you will
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2008
    Posts
    12
    Its a real world project .... i m doing it for large but cheap construction firm as a contractor.
    Anyway I'll put data in tables asap.

  9. #9
    Join Date
    Jul 2008
    Posts
    12
    The data in the table are generic and can be added... but I created the entities for each of the tables that I thought were necessary. I just want to
    know if these tables are enough and can be used to derive prime owner contact, Prime consultant and Prime consultant contact.
    Notice the Owner is referred to as Client in the tables.
    let me know if this helps
    thanks ahead of time.
    Attached Files Attached Files

  10. #10
    Join Date
    Jul 2008
    Posts
    12
    Hoping to get some help with this still.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure, what help would you like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2008
    Posts
    12
    I am still where I left off on Friday It was a nice weekend full of rain . Hope yours was good as well.
    Quote Originally Posted by Heminem
    The data in the table are generic and can be added... but I created the entities for each of the tables that I thought were necessary. I just want to
    know if these tables are enough and can be used to derive prime owner contact, Prime consultant and Prime consultant contact.
    Notice the Owner is referred to as Client in the tables.
    let me know if this helps
    thanks ahead of time.
    Attached Files Attached Files

  13. #13
    Join Date
    Jul 2008
    Posts
    12
    Also Wanted to know what is the best techniques you use to figure out which tables and relationships need to exist to meet certain rules.

Posting Permissions

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