Could this circular data reference be designed better?

Question

I have data about colors. Some of those colors consist of a number of colors combined. One of those combinations can theoretically be another combined color. In the case of a combined color, I need to define a few attributes about the "child" colors.

I currently have my database defined like so:

COLOR
 !Id
 +MultiColorGroupId

MULTICOLOR
  !Id
  +MultiColorGroupId
  +ColorId
  +Type

This can contain all the information I need in a pretty clear way. The only thing I'm thinking that could happen is that I get a circular reference where a color has itself as childColor. Currently the only way to catch this is by building a check on insert in either the database or the application. As the circle reference could be arbitrarely deep, and the current structure can potentially be rather wide i would prefer a more deterministic data structure.

Are there better ways to define this?

Answer 1

If you have an interface (other than SQL) to these tables, you could easily exclude all "illegal" options from the available selections. Otherwise on-insert and on-update triggers or check constraints could be used.

Answer 2

I think you have to do this in code, although I would caution against triggers - as you point out the cost of the triggers are going to be a lot worse than a linear relation depending on how complex your structures are.

In code with a collection it is a very simple problem to solve.

Really your table definitions cannot define this scenario out, it is a rule that is a property of the data itself, not the storing structure.

As for the data structure, I came up with the same structure.

Answer 3

If the depth can be arbitrary then you cannot solve the problem in the data structure. One thing to consider is if it is a problem if the self-reference is in fact stored in the database. Perhaps it should be up to the retrieving code to ensure it doesn't read the data infinitely.

I had a similar problem with food recipes. It takes yogurt to make yogurt (to pick a common example), so we had to support storing that, and put logic on the retrieval to ensure that a given ingredient is only exploded once.