Mapping a difficult relationship

I have a very complex relationship that I need to figure out how to model:

  • There are 20 regions
  • There are 80 companies
  • There are 20 specializations

Each company has a different salary for each specialization in each region. To make it more clear here is an example:

Company X pays $30 an hour for the specialization Y in region Z.
Company X pays $20 an hour for the specialization L in region Z.
Company X pays $25 an hour for the specialization Y in region K.
Company C pays $27 an hour for the specialization Y in region K.

Etc, hope you get the problem.

This relationship is very difficult to map, I would love to get your input.

All these salaries will change on a yearly basis. I would love to be able to do easy queries on this data.

Hi @alfred,

Yeah, that is indeed tricky. I’d probably add a JSON field to the Company model and create a UI extension to populate it by fetching all available regions and specializations via the Delivery API.

This would leave you with each company instance having a JSON object that has all the available regions and specializations as keys waiting for the editor to fill in the necessary values in a similar fashion they’d fill in values to an Excel chart. This would obviously require some UI work, but it’s nothing too complicated.

EDIT: I just noticed you’d like to do queries. JSON is not much help there, I’m afraid.

1 Like

I would create a content type and name it “Contract” or something similar. That would have a name, an amount ($) and three reference field that link the contract to a company, a specialization and a region. Those would be three other content types if you need metadata (as I assume you need). The problem is you could only query contracts using one link_to_entry search parameter at a time, Contentful doesn’t support more. It depends on your scenario whether it would be possible in your client application to fire three queries to estract all contracts that link to a specific company / a specialization and a region, and merge the results afterwards.

If you need to store payment entries those would simply link to a contract and that’s super easy to query and include on the payment entry type as a drop-down.

Hope this helps.

1 Like

I like this solution! But I don’t understand the last part? Do you mean that the contract has a field for the payments or what do you mean?

Sorry for the late reply, I’ve been travelling. The last part means that I don’t know what you want to use this for. It is just to store those relationships about the salaries? Then ignore the last part, you have a model to store just the contracts and that’s it. But if you want to use contentful as a database to add also payments events (meaning adding an entry every time one employee is paid on the basis of a contract) then you can create another content type (the payment event) that references the contract, which will be one field, and -I guess- also a reference to the person who’s getting paid, the date and so on. Hope this helps.