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

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.

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.

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?

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.

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?

CorpEmployee contains an EntityID. So including both in your ProjectEntity table is redundant, and invites relational integrity issues.

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.

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.

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.

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?

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.

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.
Of which you would derive
When it should be:
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.

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:

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':

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:

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.

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.