1

Context:

  • I have 3 unrelated entities (tables) Entity1, Entity2 and Entity3
  • I have a table called Operation, which can have multiple targets.
  • A target for an operation can be Entity1, Entity2 or Entity3.

To model this structure, I first modeled it like this (Approach 1):

Table OperationTarget

  • FK to Operation (Not Nullable)
  • FK to Entity 1 (Nullable)
  • FK to Entity 2 (Nullable)
  • FK to Entity 3 (Nullable)
  • A constraint that enforces only one of them will be set, and others will be Null

Then, I didn't like the idea of creating a table where at each row, 2 of 3 columns will always be Null, so I created a different approach (Approach 2):

Table OperationTarget

  • FK to Operation (Not Nullable)

Table OperationTargetEntity1

  • FK to OperationTarget(Not Nullable)
  • FK to Entity 1 (Not Nullable)

Table OperationTargetEntity2

  • FK to OperationTarget(Not Nullable)
  • FK to Entity 2 (Not Nullable)

Table OperationTargetEntity3

  • FK to OperationTarget(Not Nullable)
  • FK to Entity 3 (Not Nullable)

This approach feels better in terms of normalization, but in practice, this will make my codebase more complex, and also my queries more complex, as I will be needing joins. I need the parent table there for other application requirements.

I am currently going with approach 2. Is approach 1 something I should be avoiding at all costs, or when justified by simplicity and performance, it would be OK to go with it?

I've been reading on the topic for hours, some people don't like using Nullable columns at all, some say it's OK when application requires them (when it is possible you don't know the value for a column for a specific row, for instance where a user optionally can provide their birth date), but not sure if approach 1 is justifiable here, or should be avoided.

2
  • Follow a published academic textbook on information modelling, the relational model & DB design. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Ask a specific question where stuck. You are asking us to rewrite one. How to Ask help center
    – philipxy
    Commented Sep 11, 2020 at 22:23
  • 1
    @philipxy, he didn't ask us to rewrite one and i think he has studied academic courses before. He asked which of his two approaches is correct from normalization point of view.
    – Arash
    Commented Oct 21, 2021 at 8:47

1 Answer 1

2

I can't see the entire data model or exactly the context of "Operation" so I can't tell you if there's a better solution, but in general the second approach is preferable, any complexities it creates can be handled by creating a view that creates a simple presentation of the data without requiring someone to even ponder the joins that occur behind the scenes. Any insert/update/delete procedures only need to be programmed once, the additional complexity will be minimal.

Yes, more tables... BUT:

  1. Those tables will be narrow and small.
  2. You would probably build some sort of filtered index on those columns to facilitate searches, and they would look remarkably identical to the tables you've created. But in creating them you give more meaning to them than just breaking rules that exist for a good reason in order to save a few lines of code.
  3. If in the future OperationTarget not has more "targets", much easier to add another table instead of more columns to the base table.

I'd also look harder at this to see if this is a good application for exclusive subtypes, as I said I don't have a full picture of your data model so it would be something you'd have to evaluate on your own.

4
  • Hi, if we create join table for each entity to avoid mutually exclusive foreign keys, why do we need filtered index? (sorry if i didn't understand BUT number 2)
    – Arash
    Commented Oct 21, 2021 at 8:13
  • And is there a noticeable difference in terms of performance between two approaches?
    – Arash
    Commented Oct 21, 2021 at 8:58
  • @Arash - tag my name in the replies so I'm alerted when you make a comment. For your first question, the difference in physical implementation is your data would be stored twice in either case. The differences in performance would most likely be negligible. The biggest benefits are 1. that the columns are understood to be optional 2. Those relationships can have unique attributes if necessary 3. Those tables can also be used in other parts of the data model to enforce rules
    – user212533
    Commented Oct 21, 2021 at 14:28
  • Thanks @bbaird. I didn't know that when i comment on your answer, SO doesn't alert you. I will tag next time.
    – Arash
    Commented Oct 21, 2021 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.