-1

I work in cattle production and I am learning about database design with postgreSQL. Now I am working on an entity attribute relationship model for a database that allows to register the allocation of the pastures in which cattle graze. In the logic of this business an animal can be assigned to several grazing groups during its life. Each grazing group in turn has a duration and is composed of several pastures in which the animals graze according to a rotation calendar. In this way, at a specific time, animals graze in a pasture that is part of a grazing group.

I have a situation in which many grazing groups can be assigned to many animals as well as many pastures. Trying to model this problem I find a fan trap because there are two one-to-many relationships for a single table. According to this, I would like to ask you about how one can deal with this type of relationship in which one entity relates to two others in the form of many-to-many relationships.

I put a diagram on the problem.

model diagram

Thanks

Cid
  • 14,968
  • 4
  • 30
  • 45
Danilo P
  • 29
  • 1
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Feb 19 '20 at 10:19
  • There are many methods to do & diagram ER design. Please tell us what yours is. (Eg textbook name, edition & section.) Please ask re the 1st place you are stuck. Where exactly here? Tables (sometimes lines--maybe that's so here) represent relation(ship)s/associations. FKs/participations get called "relationships" but are not. When giving a relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. When clarified this will be a faq. At start each M:M relation(ship)/association gets an association table. – philipxy Feb 19 '20 at 10:34
  • [What](https://stackoverflow.com/a/25577004/3404097) "[fan](https://stackoverflow.com/a/44736822/3404097) [trap](https://stackoverflow.com/a/45557994/3404097)"? – philipxy Feb 19 '20 at 10:51

1 Answers1

0

Traditionally, using a link table (the ones you call assignment) between two tables has been the right way to do many-to-many relationships. Other choices include having an ARRAY of animal ids in grazing group, using JSONB fields etc. Those might prove to be problematic later, so I'd recommend going the old way.

If you want to keep track of history, you can add an active boolean field (to the link table probably) to indicate which assignment is current or have a start date and end date for each assignment. This also makes it possible to plan future assignments. To make things easier, make VIEWs showing only current assignment and further VIEWs to show JOINed tables.

Since there's no clear question in your post, I'd just say you are going the right way.

Vesa Karjalainen
  • 1,087
  • 8
  • 15