CODEWITHSUNDEEP
database-designmany-to-manyrelationshipentity-relationshiprdbms
Caio
Caio

Reputation: 3215

Many-to-many relationship with predefined types

Each process has multiples operations. Each operation has an unique duration (seconds) depending on the process it belongs, an operation may belong to multiples process.

enter image description here

The problem is that I have to use a list of predefined "types" and each process/operation must belong to the same "type". In other words, a process of "type A" can not have operations of "type B", the same thing goes on the other side.

I tried the following approach without success. Any clues?

enter image description here

Upvotes: 0

Views: 86

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

-- type common to process and operation 
--
po_type {type_id, type_name}
     PK {type_id}
     AK {type_name}


operation {operation_id, operation_type}
       PK {operation_id}
       SK {operation_id, operation_type}
       FK {operation_type} REFERENCES po_type {type_id}


process {process_id, process_type}
     PK {process_id}
     SK {process_id, process_type}
     FK {process_type} REFERENCES po_type {type_id} 


-- operation_process
-- process_operation_no is an integer (1,2,3 ..) for each process_id
--
op_proc {process_id, process_operation_no, operation_id, the_type, duration}
     PK {process_id, process_operation_no}
    FK1 {process_id, the_type}   REFERENCES process   {process_id, process_type}    
    FK2 {operation_id, the_type} REFERENCES operation {operation_id, operation_type}

Notes: PK = primary key 
       AK = alternate key (use unique constraint/index)
       SK = superkey      (use unique constraint/index)
       FK = foreign key

I have allowed here for an operation to repeat in the process, not sure if that makes sense in your model -- if not simply drop the process_operation_no and use operation_id in the PK.

EDIT

Bit nicer by keeping the type_id name everywhere; also without process_operation_no -- not allowing the operation to repeat within the process.

po_type {type_id, type_name}
     PK {type_id}
     AK {type_name}


operation {operation_id, type_id}
       PK {operation_id}
       SK {operation_id, type_id}
       FK {type_id} REFERENCES po_type {type_id}


process {process_id, type_id}
     PK {process_id}
     SK {process_id, type_id}
     FK {type_id} REFERENCES po_type {type_id} 


op_proc {process_id, operation_id, type_id, duration}
     PK {process_id, operation_id}
    FK1 {process_id,   type_id} REFERENCES process   {process_id,   type_id}    
    FK2 {operation_id, type_id} REFERENCES operation {operation_id, type_id}

Upvotes: 1

tempusfugit
tempusfugit

Reputation: 437

I think this looks like a classic case of ternary relationship.

Eg:

Professor <-> Subject <-> Course

enter image description here Source:Data Modeling and Database Design By Richard W. Scamell, Narayan S. Umanath

Now, Process, Operation, and Type are three entities having a ternary relationship.

The logical schema will have this ternary relationship. It will decompose into multiple m:n OR 1:n relationships (depending on the domain constraints). After that, you can model them as any other m:n in the schema.

For more, you can refer to the

Section 5.5.1 Decomposing Ternary and Higher-Order Relationships

from the same book.

Also, you can see this:

Analysis of Binary/Ternary Cardinality Combinations in Entity-Relationship Modeling

Upvotes: 1

Related Questions