Re: Using a seperate table to 'join' make a table relation

On Jan 10, 5:46 pm, JOG <j..._at_cs.nott.ac.uk> wrote:
> junction table - instersection table - associative entity - all
> different names for a setup where a relation contains two attributes
> which are foreign keys, referencing tuples in other tables. And very
> useful stuff in database design they are too, so if you discovered
> them yourself, much kudos. As with everything, we're never the first
> to think of things, but its a good sign hey ;)

My perspective is that tables frequently arrange into a lattice structure. Consider

table Orders (

   ord# integer,
   ...
)

table Items (

   ord# integer,
   item#,
   ...
)

Clearly, these two are in one to many. Pretty straightforward, so far... If we add Invoices

table Invoices (

   ord# integer,
   inv# integer,
   ...
)

however, things start to get more interesting. Each invoce informally corresponds to a partially fulfilled order, so that sometimes one order is covered with more than one invoice. Naturally there is a join table between Invoices and Items (which we better start calling OrderItems)

table InvoiceItems (

   ord# integer,
   inv# integer,
   ...
)

Now, the lattice interpretation:
i. Inner union of Invoices and OrderItems contains all the ord# occuring in the Invoices or in the OrderItems. If database design were entirely denormalized, then OrderItems and Invoices tables duplicated all the columns from Order, and the ineer union was the Order table. With normalized design, however, the Order table would contain more columns. Therefore, the Orders table is larger (in the lattice order sence) than the inner union of Invoices and OrderItems. ii. Similarly, join of Invoices and OrderItems is a table that is smaller than InvoiceItems. Received on Fri Jan 11 2008 - 03:42:06 CET