1

I need to realize a software to manage some projects. Every project starts after a offer approvation. A offer could have many revision, but only the last one offer is the parent of the project.

Example:

price_quotation_a => project

OR

price_quotation_a
price_quotation_b
price_quotation_c => project

My idea is to realize only one table, with a flag (type) to distinguish order from project. A second column (parent) to specify for every order wich offer is the parent. The problem is that offer and project have the same data.

+----+------+--------+------+----------+--------------+--------------+
| id | type | parent | name | revision | project_name | project_desc |
+----+------+--------+------+----------+--------------+--------------+
|  1 |    0 |      0 | O001 | A        | Project01    | Project_desc |
|  2 |    0 |      0 | O001 | B        | Project01    | Project_desc |
|  3 |    1 |      2 | P001 | NULL     | Project01    | Project_desc |
|  4 |    0 |      0 | O002 | A        | Project02    | Project_desc |
|  5 |    1 |      4 | P002 | NULL     | Project02    | Project_desc |
+----+------+--------+------+----------+--------------+--------------+

Operatos must insert worked hours both for order or offer so i need a list with both entity. Something like:

+----+-------------+----------+----------------+-------+
| id |    date     | operator | order/offer ID | hours |
+----+-------------+----------+----------------+-------+
|  1 | 01-010-2002 |       12 |              1 |     5 |
|  2 | 01-010-2002 |       12 |              2 |     6 |
|  3 | 01-010-2002 |        5 |              1 |     4 |
+----+-------------+----------+----------------+-------+

Shout if I haven't bee clear. It's the right way?

2
-- Employee (aka operator) EMP exists.
--
employee {EMP}
      PK {EMP}
-- Customer CST exists.
--
customer {CST}
      PK {CST}
-- On date RFQ_DTE, request for quotation
-- number RFQ_NO was received from customer CST.
--
rfq_in {CST, RFQ_NO, RFQ_DTE}
    PK {CST, RFQ_NO}

FK {CST} REFERENCES customer {CST}
-- Project PRO was started on date PRO_DTE
-- in response to request for quotation
-- number RFQ_NO from customer CST.
--
project {PRO, CST, RFQ_NO, PRO_DTE}
     PK {PRO}
     AK {CST, RFQ_NO}

FK {CST, RFQ_NO} REFERENCES rfq_in {CST, RFQ_NO}
-- Offer revision REV for project PRO
-- was created on date OFR_DTE.
--
offer {PRO, REV, OFR_DTE}
   PK {PRO, REV}

FK1 {PRO} REFERENCES project {PRO}
-- On date OW_DTE employee EMP spent OW_HRS hours
-- working on offer revision REV for project PRO.
--
offer_work {PRO, REV, EMP, OW_DTE, OW_HRS}
        PK {PRO, REV, EMP, OW_DTE}

FK1 {PRO, REV} REFERENCES offer {PRO, REV}

FK2 {EMP} REFERENCES employee {EMP}
-- Offer revision REV for project PRO
-- was accepted and approved on date APR_DTE.
--
accepted {PRO, REV, APR_DTE}
      PK {PRO}

FK {PRO, REV} REFERENCES offer {PRO, REV}
-- On date PW_DTE employee EMP spent PW_HRS hours
-- working on project PRO.
--
project_work {PRO, EMP, PW_DTE, PW_HRS}
          PK {PRO, EMP, PW_DTE}

FK1 {PRO} REFERENCES accepted {PRO}
FK2 {EMP} REFERENCES employee {EMP}

Create a view to report work hours.

CREATE VIEW work_hours
AS
SELECT OW_DTE AS DTE
     , EMP
     , OW_HRS AS HRS
     , PRO
     , 'O'    AS WRK_TYPE
     , ('offer revision ' || REV) AS DETAIL
FROM offer_work
UNION
SELECT PW_DTE AS DTE
     , EMP
     , PW_HRS AS HRS
     , PRO
     , 'P'    AS WRK_TYPE
     , 'some project work' AS DETAIL
FROM project_work
;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.