agusgambina agusgambina - 6 months ago 20
SQL Question

Database entity model design with circular relationships

I am designing a database entity model. I want to avoid circular relationships, but I am not sure how to do it in the following case:

The system is for tracking the developers tasks and clients purchases. Each product has one and only one kind of technology associated and a quantity of hours. Clients are able to buy different kind of products. A task has one and only one technology associated.

These relationships are important because after submitting the data I want to be able to query if a client is using more hours in certain technology than the hours he purchased or on the opposite side, if he bought hours in another technology that he is not using. Also I want to query how developers occupy their hours in different technologies or which technologies are associated with a developer.

Here is my design, but I think the circular relationship is something I should avoid and I should find other way to solve it.

enter image description here

Which should be the standard way to solve this?




With the Alex suggestion this would be the new diagram

enter image description here

Answer

Currently your logic does not allow linking of specific purchase order or product to task.

I propose:

Developers |-- 8 Tasks 8-- Purchases
Clients |-- 8 Purchases 8--| Products 8--| Technologies

Assumptions:

  • Your company has a product catalogue from which customers can choose to purchase specific products.
  • Each product comes with a certain amount of development hours allocated to it (products.hours field).
  • Developer is than assigned to fulfil purchase requirements (product customizations etc.), which are split into tasks. The hours spent on the task are recorded.

Reporting Requirements Checklist:

You can find out allocated development hours for each product and technology customer purchased.

SELECT *
FROM Purchases
    JOIN Products
        JOIN Technologies
WHERE client_id = 'customer'

You can find out how many hours are spent on each product and associated technology.

SELECT *
FROM Tasks
    JOIN Purchases
        JOIN Products
            JOIN Technologies
WHERE client_id = 'customer'

By linking tasks to purchases you will be able to find out what products / technologies customer is using as well as which specific product creates what tasks.

If the developer is required to work on products / technologies that are not linked to specific customer, you should be able to add your own company to the cutomer list and assign purchase orders / tasks to it and track dev time this way.

Please note that the above is based on my limited undertstanding of what is likely a much larger requirements scope. If you decide to follow my suggestion check that it meets all other application/reporting requirements.

Comments