Database Design – How to Interpret Ternary Relationship

database-designdatabase-diagramserd

I have tried to search online on how to interpret these ternary relationships but haven't found any clear answer. Since I don't understand how to interpret it I am having trouble changing it into relations as well. In an example like this:

How exactly would I interpret this? For instance:

The line with an arrow going into Works_in3 from the Employee entity, is that pointing to the Departments entity or the Locations entity? So is it implying that an Employee can only work in one department or is it implying that an employee can only work in one location or does it imply both?

Best Answer

This kind of graphical representation is not one I use, so I may have to guess a bit, specially for the small arrow you can see between employees and works_in. I think you can read it as a 0,1 relation.

You can implement this model over 4 tables, like this : (#PK / ~FK)

employees(#ssn, name, lot)
departments(#did, dname, dudget)
locations(#address, capacity)
works_in(#ssn, ~did, ~address, since)

Related Solutions

How to design relationships for variant data

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

How to manage many-to-many relationships when there is also a hierarchy among the many

Martin,

If I am understanding correctly, the employee can be associated with one or more tasks, phases, and projects. This implies a many to many relationship between an employee and a task, phase, or project. If this is the case, throw out the idea of the assignment table completely. Instead, create three different junction tables that can be used to establish the many to many relationships: EmployeeTask, EmployeePhase, and EmployeeProject.

The Task, Phase, and Project tables will allow for relationships to be established as you have defined in your post. The three junction tables will enable an employee to be included in the hierarchy at any of the three levels.

Your SELECT statements will require LEFT OUTER JOINS with the junction tables to pull back the appropriate employee information, which is fine. Adding the junction tables will provide a much more normalized database design.

Best Answer

Related Solutions

How to design relationships for variant data

How to manage many-to-many relationships when there is also a hierarchy among the many

Related Question