Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Person - Company - Project Database design

    Hi Forum,

    I am trying to design a Database for an engineering consultancy and am fairly new to Database Design.

    I am trying to keep track of several fairly independent entities being:

    1) People
    2) Company/Corporations
    3) the Consultancy Projects

    The issue I am grappling with is that the Clients are both People and Companies, and the project contacts can again be People and/or Companies.

    What I have is:
    a base Person Table.
    a base Company Table, that links to Company Office, Company Employee's [which links to the Person Table], and Company Office Employee which links the two.
    a base Project table that links to a Project Contacts table.

    My questions is what is the best way to link the Company and Person information to the Project Contacts table?

    Should the Project Contacts table have foreign keys for both the Person and the Company, or should it be split into 2 tables?

    Thanks
    Wedgetail

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Take a look at the Party design pattern:
    A Universal Person and Organization Data Model

    The key point is to create a Party table that represents the superclass of companies and people. Don't duplicate columns between Company / People but instead put the common columns in the Party table.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You've chosen a pretty challenging task for your first database design...
    I highly recommend that you get an experience DBA to design the model for you. Its either that, or you'll be hiring one to fix it for you later, and that will be a much larger task.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2009
    Posts
    29
    Thanks Blindman for your comments. I cant believe it has been a year since I kicked this all off. I have been sporadically piecing together the different areas of the database, and assembling datafiles of base information, and learning about database design.

    However, this one question is still causing me issues, and im not sure I have the best solution. Currently, I have used the Entity or Party model, which has the following layout.

    Entity : [IDEntity, EntityTypeID, StartDate, EndDate] - EntityType : Person, Corporation.
    Project : [IDProject, ProjectNumber,StartDate,EndDate]
    CorpEmployee : [IDCorpEmployee, EntityID(Corp), EntityID(Person), StartDate, EndDate]

    ProjectEntity : [ID, ProjectID, EntityID, CorpEmployeeID, StartDate, EndDate]

    It is the ProjectEntity table I am not sure about. If there is just an individual or corporation, the value would be placed in the EnityID column. However, if there was both, then the CorpEmployeeID would be added.

    Is this the correct approach?

    Also, would using a view in the database when searching on this table then be the best option?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Can an employee be in more than one corporation?
    If not, then you should drop the CorpEmployee table and just store the employee's corporation membership in the Entity table.

    Views are useful for translating a complex physical database design into a logical design more suitable to interaction with the user interface.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2009
    Posts
    29
    Thanks for your reply blindman.

    Unfortunatly, the answer to your question is yes, a person [employee] can work for more than one corporation.

    In the current database, we have the issue that the same person is listed mulitple times under different corporations. Whilst normally a person will only work for one corporation at a time, we have the issue that we may work with the same person, but that they change corporations over the years.

    There is also the issue that there are a few people we deal with, that work for several corporations at the same time.

    So given that this is the situation, is the including of an EntityID column, and a CorpEmployeeID column the best solution or do you have another suggestion?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    CorpEmployee contains an EntityID. So including both in your ProjectEntity table is redundant, and invites relational integrity issues.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2009
    Posts
    29
    Thankyou for your reply.
    Well I was intending to put a restraint on the Table so that only one of the columns could have a value.


    However, you seem to be suggesting an alternative where:

    1) If there is both a Person and Corporation, I enter both values in the CorpEmployee table.

    2) If there is a person only, I place their EntityID in the CorpEmployee table, and leave the corporation column "null".

    3) If there is a corporation only, I place their EntityID in the CorpEmployee table, and leave the person column "null".

    I would then be able to drop the EntityID from the ProjectEntity table.


    Therefore the table structure would be:
    Entity : [IDEntity, EntityTypeID, StartDate, EndDate] - EntityType : Person, Corporation.
    Project : [IDProject, ProjectNumber,StartDate,EndDate]
    CorpEmployee : [IDCorpEmployee, EntityID(Corp), EntityID(Person), StartDate, EndDate]

    ProjectEntity : [ID, ProjectID, CorpEmployeeID, StartDate, EndDate]

    Am I correct in what you were implying?

    I can see why this would be the best solution, thinking it through. Although where just an individual or corporation is linked to the project, it seems slightly redundant, it does simplify linking everywhere. It means there is no view required, and all contacts link to the CorpEmployee table, rather than the Entity table.

    Thankyou so much for your assistance.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You've lost me now.
    That is not what I was suggesting.
    Every row in your CorpEmployee table should contain both a Person and a Corporation value. It implements a many-to-many join between the two entities. Neither of those values should allow nulls, and together they should have a unique constraint placed on them.
    Then you only need to store CorpEmployeeID in the ProjectEntity table, from which you can derive both the Person and the Corporation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2008
    Posts
    277
    Wedgetail,

    I think part of the confusion is arising from the fact that you may be conflating the concepts of a "client" and a "contact". These are two distinct roles that need to be accounted for.

    Your party model will look something like this:

    party: party_id, first_name, last_name, company_name, party_type
    employment: employer_id, employee_id, start_date, end_date

    Now for projects:

    project: project_id, project_name, client_id, start_date, end_date
    project_contact: project_id, party_id

    project.client_id references a row in the party table, and indicates who the project is for. project_contact holds any contacts (whether a person or another company) associated with the project.

    If you need to enforce that a project contact is an employee of the client, then you may wish to relate project_contact to employment instead of party, and have some kind of check that the employer_id matches the client_id.

  11. #11
    Join Date
    Feb 2009
    Posts
    29
    Thankyou for your comments.

    Maybe this would be better explained with some examples of what needs to be modelled/included.


    On a Project, we may work with the following types of people.

    [Person & Corporation]
    1) John Smith from the Local Council.
    2) Alex Butler a Structural Engineer from ABS Structural Engineering.
    3) Lisa Johnson an Architect from LJ Architecure.

    [Person Only]
    4) David Jones [Client]
    5) Sarah Citzen [Neighbour of Client]
    6) James Brown [Neighbour of Client]

    [Corporation Only]
    7) Bureau of Meterology [Obtain data from, but call centre environment and dont deal with an individual].

    All of these people/corporations are Contacts for the project.

    Whilst I could enter the EntityID for the [Person & Corporation] seperately, I really need them to be the one combined contact as any correspondance, meetings etc are happening with the Person & Coporation at the same time.

    The Base tables being:
    Entity : [IDEntity, EntityTypeID, StartDate, EndDate] EntityType : Person, Corporation.
    Project : [IDProject, ProjectNumber,StartDate,EndDate]

    So as I see it, I have two options:

    OPTION 1
    The Project table has BOTH an EntityID [Person OR Corporation only] or CorpEmployeeID [Person & Corporation], with one column being null.
    CorpEmployee : [IDCorpEmployee, EntityID(Corp), EntityID(Person), StartDate, EndDate]
    ProjectEntity : [ID, ProjectID, EntityID (Null), CorpEmployeeID (Null), StartDate, EndDate]
    ProjectEntity
    2,PJ010,NULL,John Smith & Local Council,1/1/2010,NULL
    6,PJ010,NULL,Alex Butler & ABS Structural Engineering,5/1/2010,NULL
    7,PJ010,NULL,Lisa Johnson & LJ Architecure,8/1/2010,NULL
    11,PJ010,David Jones,NULL,21/12/2009,NULL
    13,PJ010,Sarah Citzen,NULL,20/1/2010,NULL
    14,PJ010,James Brown,NULL,20/1/2010,NULL
    17,PJ010,Bureau of Meterology,NULL,2/2/2010,NULL


    OPTION 2
    The CorpEmployee table allows NULLS on the EntityID or CorporationID columns [but not both], and the Project table does not have an EntityID.
    CorpEmployee : [IDCorpEmployee, EntityID(Corp)(null), EntityID(Person)(null), StartDate, EndDate]
    ProjectEntity : [ID, ProjectID, CorpEmployeeID, StartDate, EndDate]
    CorpEmployee
    5,Local Council,John Smith,1/1/2010,NULL
    23,ABS Structural Engineering,Alex Butler,5/1/2010,NULL
    41,LJ Architecure,Lisa Johnson,8/1/2010,NULL
    43,NULL,David Jones,21/12/2009,NULL
    44,NULL,Sarah Citzen,20/1/2010,NULL
    45,NULL,James Brown,20/1/2010,NULL
    52,Bureau of Meterology,NULL,2/2/2010,NULL

    Also, thankyou futurity but I am not dealing with who the Client is for the moment. That might be a boolean value on the ProjectEntity table but should be a much simplier issue once this is resolved.

    So, my questions are:
    1) What is the better option?
    2) Or is there a third option I have not thought of?

  12. #12
    Join Date
    May 2008
    Posts
    277
    1) John Smith from the Local Council.

    I really need them to be the one combined contact
    But this is why you are having problems: they are not "one contact", they are two entirely different concepts:

    1. John Smith is a contact for a project
    2. John Smith works for the Local Council

    Consequently, when you contact John Smith, we can derive that you are also contacting the Local Council. But 2 concepts = 2 relations.

    Maybe this would be better explained with some examples of what needs to be modelled/included.

    [Person & Corporation]
    1) John Smith from the Local Council.
    2) Alex Butler a Structural Engineer from ABS Structural Engineering.
    3) Lisa Johnson an Architect from LJ Architecure.

    [Person Only]
    4) David Jones [Client]
    5) Sarah Citzen [Neighbour of Client]
    6) James Brown [Neighbour of Client]

    [Corporation Only]
    7) Bureau of Meterology [Obtain data from, but call centre environment and dont deal with an individual].
    Code:
    project_contact:
    project | contact
    --------+--------
    PJ010   | John Smith
    PJ010   | Alex Butler
    PJ010   | Lisa Johnson
    PJ010   | David Jones
    PJ010   | Sarah Citzen
    PJ010   | James Brown
    PJ010   | Bureau of Meterology
    
    employment:
    employee     | employer
    -------------+---------
    John Smith   | Local Council
    Alex Butler  | ABS Structural Engineering
    Lisa Johnson | LJ Architecure

  13. #13
    Join Date
    Feb 2009
    Posts
    29
    Thanks Futurity.

    But this is where I have problems with that concept.

    If I just add John Smith from the Local Council, then I dont know for sure if its Council or the individual John Smith that is the contact.

    eg.
    Project 1 : Working on a Project, in which I deal with Council and John Smith is the Contact.
    Project 2 : John Smith wants something done for him [Client] as an individual, and Council is not involved.
    Project 3 : John Smith engages us to do some work on behalf of Council [Client], and is the contact.
    Project 4 : John Smith is the neighbour of the Client. Again he would be a contact as an individual, and Council would not be involved.

    If I follow your suggestion, the Table would look like.
    Code:
    Project Contact
    project | contact
    --------+--------
    PJ010   | John Smith
    PJ013   | John Smith
    PJ017   | John Smith
    PJ019   | John Smith
    Of which you would derive
    Code:
    PJ010 | John Smith | Local Council
    PJ013 | John Smith | Local Council
    PJ017 | John Smith | Local Council
    PJ019 | John Smith | Local Council
    When it should be:
    Code:
    PJ010 | John Smith | Local Council
    PJ013 | John Smith | 
    PJ017 | John Smith | Local Council
    PJ019 | John Smith |
    Have I missed something in this, or have you assumed that John Smith will always work for Council?
    People change companies, and sometimes we will work with there new company and old company at the same time as well.

    Im leaning more and more to my Option 2, however the table might need to be renamed from CorpEmployee to Contacts.

  14. #14
    Join Date
    May 2008
    Posts
    277
    Project 1 : Working on a Project, in which I deal with Council and John Smith is the Contact.
    Project 2 : John Smith wants something done for him [Client] as an individual, and Council is not involved.
    Project 3 : John Smith engages us to do some work on behalf of Council [Client], and is the contact.
    Project 4 : John Smith is the neighbour of the Client. Again he would be a contact as an individual, and Council would not be involved.
    There's a couple alternatives I'd start off with. In either case, we need to identify the client in the 'project' table (which is why I mentioned this in an earlier post).

    One way would be to rename 'project_contact' to 'project_role' and include the type of role:

    Code:
    project:
    project | client
    --------+--------
    PJ010   | Local Council
    PJ013   | John Smith
    PJ017   | Local Council
    PJ019   | David Jones
    
    project_role:
    project | party      | role type
    --------+------------+----------
    PJ010   | John Smith | client rep
    PJ017   | John Smith | client rep
    PJ019   | John Smith | neighbor
    At this point, I'd reconsider whether or not I even need the 'employment' table.

    Alternatively, I might rename the 'employment' table to 'party_relationship' and relate 'project_contact' to that instead of directly to 'party':

    Code:
    project:
    project | client
    --------+--------
    PJ010   | Local Council
    PJ013   | John Smith
    PJ017   | Local Council
    PJ019   | David Jones
    
    project_contact:
    project | relationship
    --------+-----------
    PJ010   | 1
    PJ017   | 1
    PJ019   | 2
    
    party_relationship:
    id | from_party    | to_party   | relationship_type
    ---+---------------+------------+------------
     1 | Local Council | John Smith | client rep
     2 | David Jones   | John Smith | neighbor
    Note that in both cases, I've omitted John Smith as a contact from project PJ013 because he's already been identified as the client.

    The second method might be better for handling subcontractors, but it does require that every contact be in some kind of relationship.

    You could also combine both methods if you need even greater detail:

    Code:
    project_role:
    project | relationship | role_type
    --------+--------------+----------
    PJ010   | 1            | permit issuer
    PJ017   | 1            | primary consultant
    PJ019   | 2            | property boundary disputant
    In this example, the Local Council might not even be the Client for project PJ010. On the other hand, it's possible that those roles are better managed as additional party relationships.

    People change companies, and sometimes we will work with there new company and old company at the same time as well.
    I've omitted the time period columns from the contact and relationship tables. These will of course be needed to track changes in status, and most likely you will need to implement some business rules and/or data integrity rules to update contact statuses when relationship statuses change.
    Last edited by futurity; 02-26-10 at 17:02.

Posting Permissions

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