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.
Which should be the standard way to solve this?
Currently your logic does not allow linking of specific purchase order or product to task.
Developers |-- 8 Tasks 8-- Purchases
Clients |-- 8 Purchases 8--| Products 8--| Technologies
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.