12

I was reading most common database design mistakes made by developer Q&A on stackoverflow. At first answer there was phrase about exclusive arc:

An exclusive arc is a common mistake where a table is created with two or more foreign keys where one and only one of them can be non-null. Big mistake. For one thing it becomes that much harder to maintain data integrity. After all, even with referential integrity, nothing is preventing two or more of these foreign keys from being set (complex check constraints notwithstanding).

I really don't understand why exclusive arc is evil. Probably I didn't understand the basics of it. Is there any good explanation on exclusive arcs?

Community
  • 1
Ali Arda Orhan
  • 385

3 Answers3

12

There is nothing evil about exclusive arcs. Simply enforce the corresponding business rule using a check constraint. Most major database management systems support check constraints (Oracle, SQL Server, PostgreSQL). If you're using a data modeling tool then there is a good chance that your tool will automatically generate the code to implement the check constraint.

Gary B
  • 129
  • 1
  • 2
7

As far as I understood it a long time ago, in an exclusive arc a table contains a number of columns that are foreign keys to other tables, but only one of these can be set at a time (due to some logical constraint on the domain following from the real world). As this rule cannot be enforced on the database a corrupt record could be created where more than one of these foreign keys has a value.

I'll make an example. Consider an application where a company keeps track of the trucks it uses to deliver goods. A truck can only be in one of three places at the same time: it can be with an employee, it can be in a parking garage or it can be in a maintenance shop. This could be modeled by having a Truck-table with employeeId, parkingGarageId and maintenanceShopId, referencing the Employee, ParkingGarage and MaintenanceShop-tables. There is no way to enforce the rule that only one of these fields is filled out on the level of the database. Bad code or somebody with direct access to the database could insert a record that has two or three fields filled, which amounts to data corruption in the database.

JDT
  • 6,360
  • 6
    The three possible truck locations are subclasses of a superclass, "truck location". There are many cases where subclasses are mutually exclusive. The challenge becomes how to model classes and subclasses in relational tables. – Walter Mitty Dec 30 '14 at 13:46
  • I agree that there are cases where using this design is justified. I can, however, also agree with the original post that this pattern is used a LOT more than it should. It has some very big disadvantages too... – JDT Dec 30 '14 at 14:11
  • 13
    Cannot a check constraint be used? For example alter table mytable add constraint myconstraint check ((col1 is not null and col2 is null and col3 is null) or (col1 is null and col2 is not null and col3 is null) or (col1 is null and col2 is null and col3 is not null)). I don't like exclusive arcs but they can be enforced with a check constraint. Of course the FK constraint must also be present. – Tulains Córdova Dec 31 '14 at 21:31
  • 1
    Hence the "complex check constraints notwithstanding" from the post quoted above. You might be able to do really sophisticated validation with check constraints or heck, even triggers, that doesn't make it a good idea or a token of good design. Imagine doing check constraints on exclusive arcs with four or five columns... Also, I'm fairly sure that not all database engines support the CHECK constraint. MySQL explicitly states in the docs that CHECK clauses are parsed, but ignored... – JDT Jan 02 '15 at 08:55
  • This source recommends exclusive arc. Thoughts? – Alex Moore-Niemi Nov 01 '16 at 17:52
  • As with most things in software development, use wisely. Be aware of the advantages and disadvantages before choosing an approach. – JDT Nov 04 '16 at 10:53
  • @AlexMoore-Niemi That article seems to only be pertinent for PostgreSQL specifically, and I'm still not convinced that it's 100% valid. – heisian May 16 '18 at 15:43
  • @TulainsCórdova That is a monstrosity. Could you not do something like: check ((col1 is not null)::integer + (col2 is not null)::integer + (col3 is not null)::integer = 1)? – Reinstate Monica Jun 27 '18 at 16:49
  • MySQL supports check constraints as of version 8.0.16 with the caveat that you can't use them on tables you use referential constraints on (ON UPDATE CASCADE, etc.) – Magnus Lind Oxlund Dec 09 '23 at 13:20
-1

The exclusive arc is very useful in the Conceptual or Logical design. It does not mean that you have to implement that way. In the earlier example the designer may decide to implement the design with three tables. One for parking location, one for the employee and one for the maintenance shop.

Trevor Cummings
  • 1