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?

Improve this question
0

3 Answers 3

Reset to default
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.

Improve this answer
8
  • 5
    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, 2014 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, 2014 at 14:11
  • 11
    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, 2014 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 2, 2015 at 8:55
  • This source recommends exclusive arc. Thoughts?
    – Alex Moore-Niemi
    Nov 1, 2016 at 17:52
9

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.

Improve this answer
-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.

Improve this answer

Your Answer

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

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