Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: circular relationships ok?

Re: circular relationships ok?

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: 2 Mar 2006 07:12:59 -0800
Message-ID: <1141312379.065049.193100@p10g2000cwp.googlegroups.com>


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.
>

I'll refer to the example you gave earlier:

>
> 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
that the OP was referring to foreign key constraints in an ER model. If so then am I correct in understanding that your diagram means that two constraints will be enforced:

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

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.

?

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

Can "concept oriented" enforce such constraints? If so, how do you draw diagrams of them without loops? Finally, what is the advantage of eliminating a loop from the diagram?

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))

--
David Portas
Received on Thu Mar 02 2006 - 09:12:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US