Re: circular relationships ok?

David Portas schrieb:
> Alexandr Savinov wrote:

>> In most existing models this constraint is not taken into account so
>> that you are free to build any meaningless data structure you want.
>> Having this constraint (ordered elements with no cycles) you have a
>> number of other advantages. It allows you to carry complex analytical
>> operations, meaningfully query data etc.
>>

>> 2. After that any relationship you define in this acyclic graph of
>> tables will use only subtables. In other words, if table A has a
>> relationship with table B then there is a common subtable C which is
>> used to establish it (there can be more subtables for complex
>> relationships). For example, a relationship between a customer and its
>> products can be implemented via common Order table:
>>
>> Customer Product
>>      \     /
>>       Order
>>

>>From your narrative I'm not certain what this diagram means. I assumed

One order has one customer.

> 2. That an Order refers to zero, one or more products that are in the
> Product table.

One order has one product.

> Am I right? If so, how would you draw a diagram that would add a third
> constraint:
>
> 3. That a Customer has zero, one or more "favourite" products that are
> in the Products table.

> ?

I will slightly modify the example.

  Customer Product

       \       /
      Order  /
         \ /
        Item

• all connections are upward arrows interpreted as follows:
  • one customer has many orders
  • one order has many items
  • one ordered item is one product
  • one product has many (ordered) items
  • one order has one customer and so on
• an upward arrow is a many-to-one relationship
• an arrow is "member of collection" relationship

The order of concepts means also multiplicity of the relationship: - if B is below A (B references A) then B->A is many-to-one (for example, many orders reference one customer). - if A is above B (A is referenced by B) the A->B is one-to-many (for example, one customer has many orders). It is also interpreted as multi-valued attribute.

Cycles are not allowed. The next reason for that is: - upper levels are more general and they do not know what kind of more specific elements will use them and hence we cannot reference to what we do not know. In particular, customers are defined *before* orders and items are defined so we cannot reference what does not yet exist. In other words, who said that for those customers there will be orders? Customers are customers and they do not need orders to be customers. (If they do need orders then they will be on the same level.)

However, the real world is more complex than the theory. For example, a customer might well has to be characterized by favorite products. Since both customers and products are unaware of what is a "favorite product" we define a common subconcept:

  Customer Product

       \      /
       Favorite(c,p)

This method should be used always in order to avoid cycles. However, sometimes we still need to have a direct downward reference. For example, we might want to store the last order for each customer (say, for performance reasons). There are two ways how to interpret this:

• It is hack or misuse. It is similar to using goto in programming. It is possible if you know what you are doing and guarantee consistency.
• The concepts with loops (mutual references) have to be placed in one level and interpreted as parts of one entity separated for some reason.

> In an ER diagram this third constraint would make a circular reference
> (see SQL example below).

I do not see in your SQL example circular references.

> Can "concept oriented" enforce such constraints?
> If so, how do you draw diagrams of them without loops?

A model with cycles is not a concept-oriented model (theoretically). It is a strong constraint but it is part of the definition. Then the question is if it is useful or not. For any model there are examples which are non-trivial and very difficult to implement. Loops and cycles can be always avoided by introducing a common subconcept. If A and B mutual reference each other then we introduce a common subconcept C:

A B
  \ /
   C

Now A does not know B and B does not know A. However, it is C who knows both and relates them. However this relationships between A and B is many-to-many. If we need one-to-one then it is done as follows:

A = B (arrows in both directions)

They are considered part of one thing.

> Finally, what is the advantage of eliminating a loop from the diagram?

Technically loops and cycles are not strictly prohibited. They are not allowed theoretically because they do not allow us to do the following things:
- having canonical semantics for the model (in particular, a finite number of dimensions).
- carrying analytical operations (aggregation etc.) - carrying out logical inference
We still may use cycles as a hack but in this case we take responsibility for their interpretation.

Thus an advantage is that having no cycles allows us to introduce canonical semantics for our model and as a consequence the database will be able to automatically manage and maintain it. Otherwise we have to this ourselves.

> CREATE TABLE Customers (customer_id INT PRIMARY KEY /* ... other
> columns */)
>
> CREATE TABLE Products (product_id INT PRIMARY KEY /* ... other columns
> */)
>
> CREATE TABLE Orders (customer_id INT REFERENCES Customers
> (customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
> KEY (customer_id,product_id) /* ... other columns */ )
>
> CREATE TABLE CustomerFavourites (customer_id INT REFERENCES Customers
> (customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
> KEY (customer_id,product_id))

I do not find loops here but I hope I answered your question.

The general idea is that cycles are evil because they do not allow us to meaningfully interpret data.

-- 
http://conceptoriented.com