Is creating a joining/bridging table the most practical and efficient way of normalizing numerous M:M relationships in a database?

Let me start with an example:

Table users:

ID | Name --------- 1, Kirk 2, John 

Table class:

ID | Class ---------- 1, MATH 2, FIN 

Now, based on what I studied so far, in order to properly normalize this database, I’d create another table, a joining/bridging table:

Table class_enrollment:

UID | CID 1     1 1     2 2     1 2     2 

Well, it works fine in these kinds of examples.

But, what if my database has 35 or 50 M:M relationships? Is it really best to create yet another 35/50 joining tables?

Cascading One-to-Many relationships modeling

Are there any drawbacks or better alternatives of this non relational model ? I note that it remains easy to understand but the concern is with the code interacting with.

First, as I was introduced to the no-SQL world, in many occasions I confronted a one-to-many relationship between entities. Today, I have a really relatively cascading example that might grow in the future.

Based on functionalities assumptions, I came up with a simple Snowflake model. Specifically a cascading one-to-many relationships with some describing data.

[User] 1 — * [Session] 1 — * [Execution] 1 — * [Report]

The data model as it seems at first is easy to deal with, but I finally found that acting on data using Mongoose (a NodeJS library) can become complex and less performant, especially in a web application context (request and response cycle). The first way of thinking is to simple refer to parents by children in a normalization fashion. Another way to implement this data model is using document embedding approach: https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/ which is easier to interact with if you just model all in one entity; However this comes at the expense of performance; Because whenever you load a user, you load all sessions, executions and reports with it.

I found a compromise between a normalized model and the one using embedded documents; Modeled here:

Normalize and embed

The compromise consist of embedding a minimal variant of the child entity like Executions of type ExecutionsMini in Sessions. While maintaining the child entity Executions separate.

The concern grows because between Users and Loggings, there might be other entities added, in a one-to-many kind or not, and this could complex more the solution (not the data model).

What are pitfalls I should be aware of with NPC/PC romantic relationships?

I will be running Rise of Tiamat as a sequel to a summer campaign I ran (which was not Hoard of the Dragon Queen). At the end of the campaign two of my PCs, a Bard and a critical role gunslinger, gained companions who are love interests for their respective PCs.

I’m not entirely sure how comfortable I am with running romance as I’ve never really had it come up before and while I am happy to do this with my players – I would like it to not derail my campaign. I also anticipate some problems as one of my players is notorious for getting annoyed with fluff like this and for reacting in undesirable ways (i.e. passive aggressive remarks, attacking the offending NPC, trying to take agency away from the other players, etc).

To summarize: What pitfalls should I be aware of with NPC/PC romantic relationships and how can I best allow them without annoying other players and or derailing my campaign?

How to properly use Nested relationships for serializers?

I am trying to display whatever data I have in my CatPerm inside my Category API endpoint. My CatPerm data consists of ‘cat’, ‘permission’, and ‘description’ whereby they are category name, permission and description respectively

Current ‘Category’ API endpoint look:

{         "name": "Travel",         "permission": [             {                 "description": "Camera is used to take photos"             }         ]     }, 

This is my desired ‘Category’ API endpoint look:

{         "name": "Travel",         "permission": [             {                 "cat": "Travel",                 "permission": "Internet",                 "description": "This is a description inside CatPerm"              }         ]     }, 

models.py

class CatPerm(models.Model):     cat = models.ForeignKey(Category, on_delete=models.CASCADE, related_name='permissions')     permission = models.ForeignKey(Permission, on_delete=models.CASCADE)     description = models.TextField()   class Category(models.Model):     name = models.CharField(max_length=50)     permission = models.ManyToManyField(Permission,                                         related_name='category_permissions',                                         through='CatPerm'                                         )   class Permission(models.Model):     name = models.CharField(max_length=100)     description = models.TextField()     platform = models.CharField(         max_length=10,         choices=PLATFORM_CHOICES,         default=BOTH,     )     classification = models.CharField(         max_length=10,         choices=CLASSIFICATION_CHOICES,         default=LOW,     )  

serializer.py

class CatPermSerializer(serializers.ModelSerializer):     cat = serializers.SlugRelatedField(slug_field='name', read_only=True)     permission = serializers.SlugRelatedField(slug_field='name', read_only=True)      class Meta:         model = CatPerm         fields = ("cat", "permission", "description")   class CategorySerializer(serializers.ModelSerializer):     permission = CatPermSerializer(many=True, read_only=True)      class Meta:         model = Category         fields = ("name", "permission")  

Updating an entity and many relationships: database vs application layer

We are working on a transformation project

It has records in table entityA and entityB, which has a relation entityAB. We need to delete records in table entityA and recreate them with some other parameters, lets say new records are rowA’. Now based on user input of matching between rowA and rowA’, we need to update records of relationAB to relationA’B, that is where rowA used to be now rowA’ will be.

For doing this there are 2 approaches:

  1. Do complete processing of taking backup, updating relation using sql procedures(pl/sql).
  2. Do transformation of records in application server and persist them in db.

We have oracle 12c server, java8, hibernate, spring boot as our technology stack.

Which approach is better?

constraints

  1. large number of records to be processed.
  2. many relations like relationAC, relationAD which also needs to be updated.
  3. transformation can take long duration, like taking backup and deleting inventory may have a duration of a week or more, because of business constraints.

Story-based adventure with factions and relationships

This is a story-based adventure program I coded by myself (forgive the length). It took a long time but it was fun. It includes loading times, changes in relationships, relationship levels, storyline and possible outcomes, user input with xbox-style options, function calling and more.

import time  def long_sleep():     for num in range(5):         time.sleep(1)         print('Loading...') def short_sleep():     for num in range(3):         time.sleep(1)         print('Loading...') #loading times      choices = [] synopsis = '''13 December 2027. A year into the zombie apocolypse, you are the young leader of a small, demoralized group in the middle of nowhere, fighting for a chance to see  light at the end of the tunnel. By September of next year, your group has grown greatly but that does not mean that your community on the brink of collapse. You must the make  tough political decisions to determine how your community fares.'''  vengeful = 'VENGEFUL.' hateful = 'HATEFUL.' disappointed = 'DISAPPOINTED.' conflicted = 'CONFLICTED/NUETRAL.' satisfied = 'SATISFIED.' happy = 'HAPPY.' prosperous = 'PROPEROUS' #relationship levels army_government = 'ARMY & GOVERNMENT' civilian = 'CIVILIANS' everybody = 'EVERYBODY' civil_great_increase = ' This greatly improves your relationship with ' + civilian + '.' civil_increase = ' This improves your relationship with ' + civilian + '.' civil_slight_increase = ' This slightly improves your relationship with ' + civilian  + '.' civil_slight_decrease = ' This slightly decreases your relationship with ' + civilian + '.' civil_decrease = ' This worsens your relationship with ' + civilian + '.' civil_great_decrease = ' This greatly worsens your relationship with ' + civilian + '.' army_great_decrease = ' This greatly worsens your relationship with ' + army_government + '.' army_decrease = ' This worsens your relationship with ' + army_government + '.' army_slight_decrease = ' This slightly decreases your relationship with ' + army_government + '.' army_slight_increase = ' This slightly improves your relationship with ' + army_government + '.' army_increase = ' This improves your relationship with ' + army_government + '.' army_great_increase = ' This greatly improves your relationship with ' + army_government + '.' everybody_great_increase = ' This greatly improves your relationship with ' + everybody + '.' everybody_increase = ' This improves your relationship with ' + everybody + '.' everybody_slight_increase = ' This slightly improves your relationship with ' + everybody + '.' everybody_slight_decrease = ' This sligtly decreases your relationship with ' + everybody + '.' everybody_decrease = ' This worsens your relationship with ' + everybody + '.' everybody_great_decrease = ' This greatly worsens your relationship with ' + everybody + '.' traitor = ' ' + everybody + ' wants you dead.' hero = ' ' + everybody + ' looks to you as a hero. '  winter = '\n' + '''29 January 2029. It is five weeks into winter and the season shows no mercy. A drought happened for a majority of the last fall and it devastated  the food supply. As your community dives deeper into the winter, you realize that your supply will run out if consumption is not altered. You could do one of two options: reduce  consumption among civilians, or ignore the risk and take a chance([ALTER SUPPLY]X}  {B[IGNORE RISK]).''' + '\n> ' alter_supply = '\n' + '''Your government is now seen as selfish. You took the risk to protect the important people and "do your best with the rest". You have suffered heavy  civilian losses but your army and government losses have been few. As a result, there is division and danger in the streets. Riots breaking out, murders, arson, all happening in your community.''' + civil_great_decrease ignore_risk = '\n' + '''Your community did better than expected during the period. That is until you ran out of food in early March. Now you rely solely on scavenging,  risking getting devoured by zombies in order to go another day. Half your community is either dead or lost with great amount of casualties from civilians and  non-civilians.''' + army_great_decrease  spring = '\n' + '''27 March 2029. One way or another, you have made through the harsh winter and now must face a totally obstacle that could jeopardize your  survival. A group of violent, hostiles target your community and threaten to overtake it if not their demands are met([DEFEND]X}  [MERGE]B}  [NEGOTIATE]A})''' + '\n> ' defend_alter_supply = '\n' + '''It was a tough battle but it was victory in the end. You sucessfully fended off the hostiles. Your army took a heavy blow but it is still intact.  Tensions are even worse though as hostile sympathizers were suppresed all around the community.''' + civil_decrease merge_alter_supply = '\n' + '''You have sucessfully merged with hostiles giving most of what is owned to them. Civilians have actually commended this call in hopes of being  treated better. Nobody was harmed.''' + civil_increase + army_slight_decrease negotiate_alter_supply = '\n' + '''You have sucessfully made a deal with the hostiles giving a large amount of resources in order to keep some peace. Your strugling-to-survive  civilians are irate with having to deal with even worse conditions. The government and army is also starting to starve. There are few to work with and things are not  looking up soon.''' + everybody_great_decrease defend_ignore_risk = '\n' + '''Your whole community got destroyed. Everyone is dead, nice one chief.''' merge_ignore_risk = '\n' + '''You have sucessfully merged with hostiles giving most of what is owned to them. Everybody sees this as the best possible option to end the  famine.''' + everybody_increase negotiate_ignore_risk = '\n' + '''The last portions of supplies have been all been swallowed by the hostiles. You are left with nothing, nice one chief.'''  outbreak = '\n' + '''22 May 2029. There was no problem in sight until one happened on this date. Civil-government relations even improved. West Nile virus has shaken your  community to the core. All healthy folks are in quarantine including you do treat the sick, which could heavily strain resources, or exile them, which could skyrocket tensions  between civilians and the government([TREAT]X}  {B[EXILE])''' + '\n> ' outbreak_treat = '\n' + '''You have treated everyone with the sickness at a cost for low meds. Another epedemic happened a few weeks later, and you lost a lot of  people.''' + army_decrease + civil_increase outbreak_exile = '\n' + '''You exiled the least useful to keep the most useful surviving. Though, resouces and supplies are stable and death is  rare.''' + civil_great_decrease + army_slight_increase population = '\n' + '''2 June 2029. Everyone is starving in a famine worse than what you imagined. Once again people are dying of starvaton and once again it is your job  to decide what happens ([POPULATION REDUCTION]X}  {B[DESPERATION])''' + '\n> ' population_reduce = '\n' + '''Remaining civilians resent this decision grealty calling it worse than cruel. However, resource control is the best way to avoid dying  out.''' + civil_great_decrease  population_desperation = '\n' + '''Your community's desperate attempts at sustaining itself failed horribly. Scavenging outside the safe zone, hunting, mining, even crimes  like robbery and cannibalism, all failed. Eventually you all died out. Nice one chief.''' independence = '\n' + '''12 July 2029. Either everyone or the civilians came to a new home to hopefully recover from their famine. However how you all were treated is a  different story. You all were treated with inferiority, and rebuttal. You all were embarassed, mistreated and underfed. Now, you all are fed up and now want to devise a plan.  Folks however, are divided on whether an ([ESCAPE]X} [CRUSADE]B} [TALK]Y}) should happen.''' + '\n> ' independence_escape = '\n' + '''It took a few weeks to thoroughly plan out the escape. You gathered as much people as you could to take part. The execution was  mostly sucessful, stripping as much supplies from the hostiles as you could. There were few deaths. The enraged, devastated hostiles kill all of your remaining folk, whether  they were in for the plan or out. You return to home and have some heavy clean up to do''' + civil_great_increase + army_slight_increase independence_crusade = '\n' + '''A lot of intelligence and thought was put into infiltrating and arming your people in a fight for their freedom. In the end the  mission was accomplished. If you altered the food supply, you did not lose much. If you took the risk, you lost a great deal of people. You take over their place and make it your  own after finding out it has closer proximity to vital resources.''' + army_great_increase + civil_slight_increase new_independence = '\n' + '''You gather up your most persuasive minds to convince the hostiles away from their inhumane ways. This surprisingly, goes better  than expected and with some initiative taken, your people and theirs now work together in unity.''' + everybody_increase  election = '\n' + '''25 October 2029. For the rest of the summer and the early autumn, you bounced back and for once resources are not a priority if you have made it this far. But, the problems never stop. A new polotician named Mr. Powell looks to takeover and "lead this community in the right direction" but you know, deep down, he is tyrannical. He  picking up steam rapidly. A lot of people want him as the leader. The election is next week and you must decide whether to sabatoge the eleciton for the safety of your  impressionable community or think of every possible compelling argument to sway your people into safety. ([SABOTAGE]X}  {B[PERSUADE])'''  + '\n> ' election_sabatoge = '\n' + '''You have sucessfully sabatoged and won the election and there is already suspicion in the results as most people picked the new person. Everyone is  irate with arson and riots breaking out all across the town. Everyone claims that the election was sabatoged but you hide the evidence that proves them  right.''' + civil_great_decrease election_persuade = '\n' + '''You try your best to convince the crowd but fail as the crowd hangs on every word Mr. Powell says. He has taken over the town. In weeks his tyrannical  overtakes the town. Cruel actions like murdering innocent outsiders, killing the children and elderly, and decapitating those who sympathize, all take effect. Your community has  become the opposite of what you envisioned.''' + army_slight_decrease upgrade = '\n' + '''14 November 2029. New Independence is now your new home, where the community thrives together on working to make the town a better place to live in. Different  types of people are divided on what should be big priority. ([ARMY]X}  [RESILIENCY]B}  [RESOURCES]Y}  [SERVICES]A})''' + '\n> ' army = '\n' + '''An EF-5 tornado directly hit your way and killed everything in its path. Nice one chief.''' resources = '\n' + '''An EF-5 tornado directly hit your way and killed everything in its path. Nice one chief.''' services = '\n' + '''An EF-5 tornado directly hit your way and killed everything in its path. Nice one chief.''' resiliency = '\n' + '''You have focused you attention at making your community hard to destroy. Additions like much tougher boundaries and an underground helped prepare your  community to sucessfully survive an EF-5 tornado directly hitting the town. But now you lost everything and will have to rebuild.''' + everybody_increase  ending_opportunity = '\n' + '''1 December 2029. After a few weeks of trying to rebuild you realize that reconstruction. You can't go back your old community beacuse there is  nothing there. So now your stuck. You went scavenging earlier before the tornado and found a living space that could keep a person going for months. However, it keeps ONE person going for months. You are left to decide if survival is really that important. ([ABANDON]X}  {B[PERSERVERE])''' + '\n> ' ending_abandon = '\n' + '''You left your community, with no leader, to die in exchange for your comfort. The community is irate and you will be killed on first sight if found.  Luckily, just before your food and supplies ran out, you found another community and you had few problems. You lived there for the rest of your life.''' + traitor ending_perservere = '\n' + '''One by one, everyone fell out. With no necessities, your community died out. You where seen as loyal for sticking with your community until the end.  Nice one chief.'''  ending_terrorism = '\n' + '''6 February 2029. Mr. Powell is back and he is out for revenge from losing the election. He and his militia are destroying everything and everyone in  sight until he gets what wants. With only a few minutes before he arrives at your city hall, you and your army must decide to ([ATTACK]X}  {B[RETREAT])''' + '\n> ' ending_attack = '\n' + '''You and your remaining army fought ferociousy against the bigger opposition. In the end, you won, but at a cost, destroyed the whole city. Few survived  being admist the exchange of explosives and gunfire. Your city is decimated but you stopped a major threat from taking over. It took two years before conditions returned to  normal''' ending_retreat = '\n' + '''You most peaceful decision and left with your most trusted peers. You wandered off into unknown and eventually fit in with another community. A few  months later, you gathered up enough men to take back your old city. When you arrived, you discovered the city collapsed with decayed skeletons everywhere walked. It was a ghost  city.''' ending_execution = '\n' + '''11 April 2030. You are about to be hanged for sympathizing against their standards. Everyone watches outside cheering and patiently awaiting your  death. When you asked for last words, you tried to ([CONVINCE THE CROWD]X}  {B[KILL MR. POWELL]).''' '\n> ' ending_death = '\n' + '''You have died. The people heard your short speech and were compelled and related to it deeply. They were so emotional that no little time wasted to  overthrow Mr. Powell's government after you were executed. Soon a new leader was chosen to lead the community and ever since, they have advanced to be one of the most expansive  good guys in the apocolypse. They even found a cure.''' + hero ending_kill = '\n' + '''Just before somebody could do something, you grabbed a soldier's AK-47 and AKed both him and Mr.Powell. You barely managed to escape the scene. You gathered  any remaining supporters to basically go on a warpath and kill as much army members as you could until they surrendered. You somehow succeedeed in this and basically took over by  force. This makes the people very unhappy but over time they realize how much you care.'''  the_end = '\n' + 'THE END'  army_relationship = 0 civil_relationship = 0 hero_traitor = 15 major_change = 2 change = 1 slight = 0.5 relationships = [army_relationship, civil_relationship, hero_traitor, major_change, change, slight] army = relationships[0] civil = relationships[1] hero_betray = relationships[2] major = relationships[3] up = relationships[4] down = relationships[4] slightly = relationships[5]  def roadblock():     roadblock = 'Please enter a valid input'        print(roadblock)         def story():     situation_winter = str(input(winter))     if situation_winter == 'X':         short_sleep()         print(alter_supply)         relationships[1] -= major         spring_alter_supply()         choices.append('chose safety over risk')     elif situation_winter == 'B':         short_sleep()         print(ignore_risk)         relationships[0] -= major         spring_ignore_risk()         choices.append('chose risk over safety')     else:         roadblock() def spring_alter_supply():     situation_spring = str(input(spring))     if situation_spring == 'X':         short_sleep()         print(defend_alter_supply)         relationships[1] -= down         sit_outbreak()         choices.append('chose defence over all')     elif situation_spring == 'B':         short_sleep()         print(merge_alter_supply)         relationships[1] += up         relationships[0] -= slightly         independence_missouri()         choices.append('chose merging over all')     elif situation_spring == 'A':         short_sleep()         print(negotiate_alter_supply)         relationships[0] -= major         relationships[1] -= major         populated()         choices.append('chose negotiation over all')     else:         roadblock() def spring_ignore_risk():     situation_spring = str(input(spring))     if situation_spring == 'X':         short_sleep()         print(defend_ignore_risk)         choices.append('chose to defend and died')     elif situation_spring == 'B':         short_sleep()         print(merge_ignore_risk)         relationships[1] += up         relationships[0] += up         independence_missouri()         choices.append('chose merging over all')     elif situation_spring == 'A':         short_sleep()         print(negotiate_ignore_risk)         ('chose to negotiate and starved')     else:         roadblock() def sit_outbreak():     situation_outbreak = str(input(outbreak))     if situation_outbreak == 'X':         short_sleep()         print(outbreak_treat)         relationships[0] -= down         relationships[1] += up         elect()         choices.append('chose aid over resources')     elif(situation_outbreak) == 'B':         short_sleep()         print(outbreak_exile)         relationships[1] -= major         relationships[0] += slightly         elect()         choices.append('chose resources over aid')     else:         roadblock() def independence_missouri():     situation_independence = str(input(independence))     time.sleep(4)     if situation_independence == 'X':         short_sleep()         print(independence_escape)         relationships[0] += slightly         relationships[1] += major         elect()         choices.append('chose to escape')     elif situation_independence == 'B':         short_sleep()         print(independence_crusade)         relationships[0] += major         relationships[1] += slightly         buff()         choices.append('resorted to violence')     elif situation_independence == 'Y':         short_sleep()         print(new_independence)         relationships[0] += up         relationships[1] += up         buff()         choices.append('chose to talk')     else:         roadblock() def populated():     situation_population = str(input(population))     if situation_population == 'X':         short_sleep()         print(population_reduce)         relationships[1] -= major         elect()         choices.append('chose survival over morals')     elif situation_population == 'B':         short_sleep()         print(population_desperation)         choices.append('tried to perservere but died')     else:         roadblock() def elect():     situation_election = str(input(election))     if situation_election == 'X':         short_sleep()         print(election_sabatoge)         relationships[1] -= major         terrorism()         choices.append('chose dirty play over clean')     elif situation_election == 'B':         short_sleep()         print(election_persuade)         relationships[0] -= slightly         execute()         choices.append('chose clean play over dirty')     else:         roadblock() def buff():     situation_upgrade = str(input(upgrade))     if situation_upgrade == 'X':         short_sleep()         print(army)         choices.append('chose army over all and died')     elif situation_upgrade == 'B':         short_sleep()         print(resiliency)         relationships[0] += up         relationships[1] += up         opportunity()         choices.append('chose resiliency and survived')     elif situation_upgrade == 'Y':         short_sleep()         print(resources)         choices.append('chose resources over all and died')     elif situation_upgrade == 'A':         short_sleep()         print(services)         choices.append('chose services over all and died')     else:         roadblock() def opportunity():     situation_end_opportunity = str(input(ending_opportunity))     if situation_end_opportunity == 'X':         short_sleep()         print(ending_abandon)         relationships[0] -= hero_traitor         relationships[1] -= hero_traitor         print(the_end)         choices.append('chose your self over all')         print('\n')         civil_left()         army_left()     elif situation_end_opportunity == 'B':         short_sleep()         print(ending_perservere)         print(the_end)         choices.append('chose everyone over selfishness')         print('\n')         civil_left()         army_left()     else:         roadblock() def terrorism():     situation_end_terrorism = str(input(ending_terrorism))     if situation_end_terrorism == 'X':         short_sleep()         print(ending_attack)         print(the_end)         choices.append('chose war over retreat')         print('\n')         civil_left()         army_left()     elif situation_end_terrorism == 'B':         short_sleep()         print(ending_retreat)         print(the_end)         choices.append('chose retreat over war')         print('\n')         civil_left()         army_left()     else:         roadblock def execute():     situation_end_execute = str(input(ending_execution))     if situation_end_execute== 'X':         short_sleep()         print(ending_death)         relationships[0] += hero_traitor         relationships[1] += hero_traitor         print(the_end)         choices.append('chose words over guns')         print('\n')         civil_left()         army_left()     elif situation_end_execute== 'B':         short_sleep()         print(ending_kill)         print(the_end)         choices.append('chose guns over words')         print('\n')         civil_left()         army_left()     else:         roadblock() def civil_left():     if relationships[1] <= -8:         print('You left the ' + civilian + ' feeling ' + vengeful)     if relationships[1] > -8 and relationships[1] < -4:         print('You left the ' + civilian + ' feeling ' + hateful)     if relationships[1] >= -4 and relationships[1] < -1:         print('You left the ' + civilian + ' feeling ' + disappointed)     if relationships[1] >= -1 and relationships[1] < 2:         print('You left the ' + civilian + ' feeling ' + conflicted)     if relationships[1] >= 2 and relationships[1] < 5:         print('You left the ' + civilian + ' feeling ' + satisfied)     if relationships[1] >= 5 and relationships[1] < 8:         print('You left the ' + civilian + ' feeling ' + happy)     if relationships[1] >= 8:         print('You left the ' + civilian + ' feeling ' + prosperous) def army_left():     if relationships[0] <= -7:         print('You left the ' + army_government + ' feeling ' + vengeful)     if relationships[0] > -7 and relationships[0] < -4:         print('You left the ' + army_government + ' feeling ' + hateful)     if relationships[0] >= -4 and relationships[0] < -2:         print('You left the ' + army_government + ' feeling ' + disappointed)     if relationships[0] >= -2 and relationships[0] < 2:         print('You left the ' + army_government + ' feeling ' + conflicted)     if relationships[0] >= 2 and relationships[0] < 5:         print('You left the ' + army_government + ' feeling ' + satisfied)     if relationships[0] >= 5 and relationships[0] < 7:         print('You left the ' + army_government + ' feeling ' + happy)     if relationships[0] >= 7:         print('You left the ' + army_government + ' feeling ' + prosperous)     print(synopsis) time.sleep(3) long_sleep() story() def choice():     print('\n' + 'You: ')     for decision in choices:         print('   - ' + decision + '\n') choice() 

How to define Views filters and relationships for custom Civicrm contact fields that are indexed via Search API?

I followed the steps on skvare to index CiviCRM contacts via Search API, then to have Views list the contacts with the search box as an exposed filter.

I need to expose a filter that filters results based on what membership type the contact has. That might require a relationship, which is currently not an option in Views for me, so I might have to write a custom relationship handler as well as the custom filter handler. I have been trying to find the code to read to use as a guide but I have been unable to figure out whether these handlers are being defined in CiviCRM, Views, Search API, or the Civicrm Entity module which I’m also using.

How do you define a custom relationship and filter handlers for custom fields on CiviCRM contacts being displayed via Search API, CiviCRM Entity, and Views?

How to handle many-to-many relationships with multiple incompatible relationship types in RESTful API?


Note this question is about when there are multiple relationship types. This is not the same as this question, where there is only a single member relationship.

Say we have persons and organizations. There are two types of relationships between them. One is employment and the other is customer.

I’d like to be able to get the persons associated with an organization based on either employment or customer, as well as the inverse, getting the organizations associated with a person based on either employment or customer.

Usually when I have relationships like this id just go with:

/persons/<person_id>/organization /organizations/<organization_id>/person 

Now, how should I represent this when the type of relationship is ambiguous, in my example it could be either employment or customer. Here are some alternatives I’ve come up with for accessing all persons who are customers of an organization:

/customers/organizations/<organization_id>/persons /organizations/<organization_id>/customers/persons /organizations/<organization_id>/persons?only_customers=true /organizations/<organization_id>/persons?relationship=customer 

Under the hood these would be modeled as four DB tables:

person <- customer -> organization person <- employment -> organization 

Are there any other ways of doing this? Which would be the canonical way? I’m not very keen on creating new object types. It might be obvious that person could be replaced with employee, or something like that, but that would have huge implications for other parts of the API design and client implementation, thus is not on the table.