Creating archive like functionality through birectional relationships

I could use some guidance in something where I may have developed myself into a corner. Here is the situation I have:

  • CPT: Communities
  • CPT: Innovators
  • CPT: Success Stories

Any Innovator or Success story needs to be tied to a Community. I’ve attempted to do this through a ACF Bidirectional relationship field. I did this instead of a taxonomy because I didn’t want them to have to manage a community taxonomy and a community post type every time a new one was added. It also makes listing associated innovators and success stories on a community page relatively simple. The problem is now that I am doing it that way, I can’t use the archive system.

What I need to be able to do is list the communities, and have them link to a list of their associated innovators or success stories depending on the intent. Ideally the titles of these listing pages can be dynamic as well. There is an old plugin called CPT-onomies that converts a CPT into a taxonomy for another but I am hesitant to use it since it has been 2 years from last update. Is there an elegant and dynamic solution to this or should I just make taxonomies?

For a visual representation, here is the design. Slides 4 and 5 are good example.

Many thanks.

Object with many-to-many relationships with multiple tables

Here’s an example for a use-case that matches what I’m trying to better understand. Say I have 3 objects I need to deal with that are similar in many ways, such as invoice, PO, and receipt. They can all have a number, and amount, etc. They can also have many similar relationships, such as line items, images attached, etc. I can think of a number of ways of modeling this.

Option 1
Make everything separate. Have tables for invoice, invoice_line, invoice_image, then po, po_line, po_image, etc. This means strong referential integrity with foreign keys and least possible number of joins, but a ton of duplication for each table.

Option 2
Have parent document table with common fields and a type field, then have invoice, po, and receipt table have a foreign key to document_id. I can then have a single document_image table. For the lines, there are again some differences but many similarities between all, so could have a document_line table with a foreign key to document_id and invoice_line, po_line, and receipt_line tables with foreign key to document_id. Here, we have less duplication, keep referential integrity with foreign keys, but start having many more joins to get all the info we need. If I have an invoice line item and wanted to get all the info, I’d need to join invoice_line to document_line, invoice, and document.

Option 3
Use separate invoice/po/receipt tables, but for image relationship (or any other) add multiple nullable foreign keys, so image would have invoice_id, po_id, and receipt_id nullable foreign keys. With this we can still enforce referential integrity but we have some fields that will often be useless polluting things, plus we now can’t make a necessary field required because they all need to be nullable. We do cut down on duplication.

Option 4
Use separate invoice/po/receipt tables, but for image relationship (or any other) have a type field and fk_id field. This way I don’t need to have multiple many-to-many tables so it cuts down on duplication, especially if you have lots of these many-to-many, since it would always be 3 tables each time. I like this option the least because you can’t have a foreign key, I pretty much not even considering it as a valid option.

I’m leaning towards option 1 or 2, I think options 3 or 4 seem like bad design and I’d likely only consider if somebody explained that there are major performance benefits. For option 1, even though there’s duplication, you can get around it with code generation, so maybe not so big a deal. But would be interested in knowing if there’s a major advantage to breaking it down like option 2.

Many to many to many custom post relationships

I want to set up a competition scoring site for horse competitions.

Each competition has many classes

Each class has 3 disciplines (show jumping, dressage and cross-country) that are scored independently of each other and totaled to create a final score with intermediary scores and placings after each discipline.

Each class has many horses

Each horse has a single rider

Each rider can ride multiple horses in all classes.

I would like to have custom fields for classes, riders, horses and competitions to store data relating to each item which suggests 4 custom-post-types for classes, riders, horses and competitions.

The only simple relationship is that each horse can only be ridden in one class.

Can I create many to many to many relationships? Or is that the wrong way to look at this.

I want to be able to show:

  • which horses a rider is riding
  • Which classes a horse is in
  • The competitors in each class with running scores showing the placings in that class

Finally I will have multiple Competitions.

I’m slightly at a loss how to build this either with plugins or otherwise.

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.