Re: Advice needed: Create a junction table, or a better way?

From: Neo <neo55592_at_hotmail.com>
Date: 29 Oct 2004 13:53:50 -0700
Message-ID: <4b45d3ad.0410291253.74ef6a14_at_posting.google.com>

> I have a table called Estimate that contains information about an estimate
> given... each Estimate my require 0 or more "SpecialEquipment"s.

Solutions having NULLs or multiple things in a field may be less generics/robust/maintainable. An intermediate mapping table may provide more flexibilty.

T_Est
ID Name Customer

-- ---------------- ----------

1 Repair Main Line New York

T_Est_Equip_Map
EstID EquipID Qty AttribX
----- ------- --- -------

1      1       5
1      4       2

T_Equip
ID Name
-- ----------
1 Dump Truck
2 Scaffolding
3 Roof Jacks
4 Crane

Below is a script to model above data with a XDb2 (experimental db):

// Create major things

CREATE2 *estimate.cls = thing;
CREATE2 *customer.cls = thing;
CREATE2 *qty.cls = thing;

CREATE2 *equipment.cls = thing;

CREATE2 *dump truck.cls = equipment;
CREATE2 *crane.cls = equipment;

// Create an estimate to repair main line for city of new york CREATE2 *;

CREATE2 it.cls = estimate;
CREATE2 it.name = repair main line;
CREATE2 it.customer = +city of new york;

// Link 5 trucks to estimate
CREATE2 *;

CREATE2 it.asm = repair main line;
CREATE2 it.cls = dump truck;
CREATE2 it.qty = +5;

// Link 2 cranes to esitmate
CREATE2 *;

CREATE2 it.asm = repair main line;
CREATE2 it.cls = crane;
CREATE2 it.qty = +2;

// Create an estimate to paint sears and wards CREATE2 *;

CREATE2 it.cls = estimate;
CREATE2 it.name = paint exterior;
CREATE2 it.customer = +sears;
CREATE2 it.customer = +wards;

// Link 1 crane to esitmate
CREATE2 *;

CREATE2 it.asm = paint exterior;
CREATE2 it.cls = crane;
CREATE2 it.qty = +1;

A view of above things in a tree is shown below:

thing
  inst: estimate
    inst: repair main line

      customer: new york
      part: dump truck 5
        qty: 5
      part: crane 2
        qty: 2
    inst: paint exterior
      customer: sears
      customer: wards
      part: crane 1
        qty: 1

A query to find an estimate that has one crane: SELECT2 %.cls=estimate & %.part=(%.cls=crane & %.qty=1);

Note: inst/cls are abbreviations for instance/class. part/asm are abbreviations for part/assembly. Received on Fri Oct 29 2004 - 22:53:50 CEST