How do I define table relations in a circular reference?

Question

In my current project I have data about colors. Each color is either a real color (with RGB values) or is a "container color" that exist out of multiple layers of colors.
This potentially creates circular references which will have to be caught at application level (but that's another question)

So I have

Colors
------
+ (PK) Id

ColorComposition
-----------
+ (PK) Id
+ (FK) MotherColorId
+ (FK) ChildColorId

When I add them to my Linq2Sql schema I get weird relations. This makes me doubt my structure.

A color should have 0 to many ColorCompositions (Color.Id => ColorComposition.MotherColorId)
A ColorComposition should have many to 1 color (ColorComposition.ChildColorId => Color.Id)

The first relation is interpreted correctly, but the second one isn't. It's in the wrong direction.
How should I define the last relation in terms of foreign keys?

Answer 1

It sounds like you are trying to store an acyclic directed graph. Try googling 'sql acyclic directed graph'. Here's the top result:

A Model to Represent Directed Acyclic Graphs (DAG) on SQL Databases

Your 'MultiColor' table has a confusing name. Consider changing it to 'ColorComposition'?

Answer 2

Add another column name like Status and put the Mother and Child in this column. use only one fkColourid in Multicolor table as per id update the status in this status column after your relaitonship work fine.