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

    Question Database Design for Engineering Counsultancy

    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.

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    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
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    May 2009
    Location
    India
    Posts
    66
    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

  4. #4
    Join Date
    Feb 2009
    Posts
    29
    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?

  5. #5
    Join Date
    May 2009
    Location
    India
    Posts
    66
    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

Posting Permissions

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