1

I'm having some bad feelings about a task, where sub-sub-sub-childs should get physical parent ids (foreign keys) to a "super parent".

So, here is the current database/orm structure

masterparent->child1->child2->child3->child4->child5->child6

always oneToMany

So it's a quite long chain. Let's take a better readable example in reversed order:

nail->getFingerPart()->getFinger()->getHand()->getHuman() (and assume every fingerPart can have multiple nails ;))

My task now wants to add a column human_id to "nails", "finger_parts" and "fingers" (and not just "hand" having it).

The main reasons for this are the many joins to get all nails from a human and so on. And the entity "shortcuts" like human->getNails() look quite ugly as well: foreach this->getHands() as hand ... foreach hand->getFingers() ...)

Although I don't think it would be a big performance issue (it's not a high performance app where every ms matters), but yea, these joins are annoying and 5-6 nested loops don't let you fall in love into the code.

But I don't know, it just feels wrong.

1.) I guess you'll run into problems with cascade deleting in doctrine.

2.) It looks like you'll mess up the structure as there will be no "source of truth" anymore (finger says it belongs to human 1, hand says it belongs to human 2) - but well, in our special case this wont happen as you won't be able to change the "human". But if this gets a common behavior to avoid joins it certainly will.

3.) In our case you can create new hands, fingers, ... where we need to set the human after this change, so we need to alter quite a lot of code (but I guess, this won't impress the stakeholder).

Any other "conflicts" here which may might happen?

Isn't there a better way like creating a view for this? And then HumanView->findAllBy(["human": 1]); to get all the nails (but with "real" writable entities - not the readonly view ones)?

bibamann
  • 2,469
  • 1
  • 11
  • 9
  • 1
    What version of MySQL? I ask because 8.0 has "recursive CTEs" which may be quite useful. – Rick James Jun 18 '19 at 13:42
  • Possible duplicate of [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – philipxy Jun 18 '19 at 23:39

1 Answers1

0

You ask about adding redundancy to your database model.

That's always possible, and it provides benefits and drawbacks. I haven't used it recently, but I certainly remember doing it in the 90s when we had this slow machines and queries took an unacceptable amount of time on normalized database models.

In the end it's a technical decision that has its costs, and the stakeholder won't care unless it directly affects the bottom line.

PROS:

  • High speed queries.
  • Simple and shorter queries, with less code. Easier to read and to write.

CONS:

  • Redundancy. And there are lots of articles about it.
  • Every single data modification now needs to be executed on a transaction, since it will always encompass multiple updates/deletes.
  • A lot of code even for simple data modification.
  • All developers need to understand and do this the right way.
  • Oh... a new application comes in that uses the database, and it's new team who'll be producing changes to it. Do they know how to do it right? Do you trust them?
  • Maybe you need to write nightly processes that will check/fix the redundancy.

Well, as you see there are a lot of drawbacks to redundancy. These are the ones I can think of off the top of my head.

Nevertheless, sometimes redundancy is the best solution, specially when you have limited hardware, or massive amounts of data.

If you go this way, make sure you understand all the drawbacks, and that you and management are willing to deal with the costs.

The Impaler
  • 45,731
  • 9
  • 39
  • 76