Hi all who can be of assistance.

I am at the start of designing a database for an engineering consultancy firm who has both corporate and private clients, and undertakes design and investigation projects.

The database will ultimately contain information on:
- various companies and individuals the firm has worked / dealt with.
- project information.
- a document control system records.
- a 'database' of technical papers, books, journals etc.
- finanical management system, including timesheet records, budgeting and accounting information and account/payments.

My questions relates to the best system to adopt at the very top level in terms of recording the Person & Company information, and how this would link to project contacts.

From previous information I have searched for on this, it seems an 'Entity' or 'Party' design would be the most appropriate in the following structure.

Entity Table containing:
- EntityID
- Entity Type [Person or Corporation]

Employee Table:
- EmployeeID
- CorpEntityID [Corporation only - from Entity Table]
- PersonEntityID [Person only - from Entity Table]

It would then seem that I need two tables for the Project Contacts, being:
Project Contact Single.
- ProjectID
- EntityID [Person or Corporation only]

Project Contact Dual
- ProjectID
- EmployeeID [from Employee Table]

I then thought I would need to create a view 'table' to join the two together so I can list all the project contacts together.
Project Contacts View Table
- ProjectID
- PersonEntityId [name columns etc]
- CorpEntityID [name columns etc]

Would this be a valid approach to this situation?
Can a lower level table reference a view in most DBMS? [Currently thinking of using either PostgreSQL or MSQL server]
Any other comments would be appreciated.

As an alternative - You could drop the Project Contacts View Table - and store all relavant information in the Employee table.
Relevant SQL statements would return the data in a usable dataaet

some questions.

Presuming that the employees are employees of the consulting firm, they may be allotted either to a corporation or an individual. In the employee table, the fields corpentityid and personentityid are really entityid (foreign key) with entity type being known from the entity table.

Second, technical journals etc, better to follow the guidelines of the most important journals in your trade. Most library systems would have a line of rubrics or keywords (sometimes called "selects").

Finacial management systems, a good idea may be to link to a standard package that has time sheet based calculations.

End

Thankyou for your response AnanthaP.

The Employee table was not going to be specific to the consulting firm, but to any company / corporation. There might be a better name / word for it.

I was going to have another table Staff for the consulting firms people, on top of the employee table. The Staff table would contain login information, staff classifications, pay rates etc [might be a few subset tables to].


In terms of the technical journals, I know I need records for:
- Journal Name,
- Paper Title,
- Paper Author,
- Paper Abstract,
- Keywords,
- Additional Comment [Firms review / comments on the paper].
But there are also many other reports from other firms, government departments that are not formal 'Journals', as well as product information from various suppliers.
Im not quite sure at this stage if that will all end up in one table to cover all the various document types, or spread over several.

And I think I will look at some standard timesheet systems to look into. Thankyou for the suggestion.

Finally, and knowing that the Employee table will have records of internal and external companies employees, is my concept of using two table for Project Contacts, and joining them with a 'View' table something that will work?

Project contacts could ideally become part of a single generic contacts list. Linked to their present employer and status and also separately searchable in case they change dzobs or assignments.

End