Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 6476 invoked from network); 29 Jan 2008 17:54:52 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 29 Jan 2008 17:54:52 -0000 Received: (qmail 88164 invoked by uid 500); 29 Jan 2008 17:54:41 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 88145 invoked by uid 500); 29 Jan 2008 17:54:41 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 88134 invoked by uid 99); 29 Jan 2008 17:54:41 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 29 Jan 2008 09:54:41 -0800 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS,WHOIS_MYPRIVREG X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of bradm6406@hotmail.com designates 65.54.246.153 as permitted sender) Received: from [65.54.246.153] (HELO bay0-omc2-s17.bay0.hotmail.com) (65.54.246.153) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 29 Jan 2008 17:54:24 +0000 Received: from BAY124-W26 ([207.46.11.189]) by bay0-omc2-s17.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 29 Jan 2008 09:54:16 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_a4a90b4d-fd45-4c46-b002-c354fc53e369_" X-Originating-IP: [68.148.155.17] From: Brad Moore To: Derby Discussion Subject: RE: Determine which child table a record belongs to Date: Tue, 29 Jan 2008 10:54:16 -0700 Importance: Normal In-Reply-To: <15164998.post@talk.nabble.com> References: <15164998.post@talk.nabble.com> MIME-Version: 1.0 X-OriginalArrivalTime: 29 Jan 2008 17:54:16.0465 (UTC) FILETIME=[F713C810:01C8629F] X-Virus-Checked: Checked by ClamAV on apache.org --_a4a90b4d-fd45-4c46-b002-c354fc53e369_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can I suggest changing the "Crane" table to include a CraneType column or s= omething similar? I don't know if there is a reason that's not obvious to = me why you need all of the other tables. It looks like you're using the ot= her tables to determine the type of crane, the same thing could be done wit= h one additional column in the Crane table. That would simplify your datab= ase design a lot. Unless I'm missing something. Brad > Date: Tue, 29 Jan 2008 09:46:09 -0800 > From: mz_tricsta@yahoo.com > To: derby-user@db.apache.org > Subject: Determine which child table a record belongs to >=20 >=20 > Hello All, >=20 > I have the following tables in my database -: >=20 > CREATE TABLE Crane > ( > PTTEquipmentID INTEGER NOT NULL, > LiftingCapacity INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Equipment ON DELETE CASCADE > ) >=20 > CREATE TABLE RoughTerrainCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > CREATE TABLE AllTerrainCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > CREATE TABLE TruckCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > CREATE TABLE CarryDeckCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > CREATE TABLE CrawlerCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > CREATE TABLE RingCrane > ( > PTTEquipmentID INTEGER NOT NULL, > PRIMARY KEY (PTTEquipmentID), > FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE > ) >=20 > 'Crane' is the parent table of 'RoughTerrainCrane', 'AllTerrainCrane', > 'TruckCrane', 'CarryDeckCrane', 'CrawlerCrane' and 'RingCrane'. If I have= a > 'Crane' key, how do I determine which of the child tables the key belongs= to > without querying each child table? >=20 > I greatly appreciate any effort to help me. Thank you for your time and > consideration. >=20 > Sincerely, > Tricsta > --=20 > View this message in context: http://www.nabble.com/Determine-which-child= -table-a-record-belongs-to-tp15164998p15164998.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. >=20 _________________________________________________________________ --_a4a90b4d-fd45-4c46-b002-c354fc53e369_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Can I suggest ch= anging the "Crane" table to include a CraneType column or something similar= ?  I don't know if there is a reason that's not obvious to me why you = need all of the other tables.  It looks like you're using the other ta= bles to determine the type of crane, the same thing could be done with one = additional column in the Crane table.  That would simplify your databa= se design a lot.  Unless I'm missing something.

Brad
<= br>
> Date: Tue, 29 Jan 2008 09:46:09 -0800
&g= t; From: mz_tricsta@yahoo.com
> To: derby-user@db.apache.org
> = Subject: Determine which child table a record belongs to
>
> <= br>> Hello All,
>
> I have the following tables in my datab= ase -:
>
> CREATE TABLE Crane
> (
> PTTEquipmentID= INTEGER NOT NULL,
> LiftingCapacity INTEGER NOT NULL,
> PRIMAR= Y KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Equ= ipment ON DELETE CASCADE
> )
>
> CREATE TABLE RoughTerra= inCrane
> (
> PTTEquipmentID INTEGER NOT NULL,
> PRIMARY = KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane= ON DELETE CASCADE
> )
>
> CREATE TABLE AllTerrainCrane<= br>> (
> PTTEquipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTT= EquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELE= TE CASCADE
> )
>
> CREATE TABLE TruckCrane
> (
= > PTTEquipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),=
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE> )
>
> CREATE TABLE CarryDeckCrane
> (
> PTTE= quipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> = FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )>
> CREATE TABLE CrawlerCrane
> (
> PTTEquipmentID = INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY= (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )
>
= > CREATE TABLE RingCrane
> (
> PTTEquipmentID INTEGER NOT NU= LL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipment= ID) REFERENCES Crane ON DELETE CASCADE
> )
>
> 'Crane' i= s the parent table of 'RoughTerrainCrane', 'AllTerrainCrane',
> 'Truc= kCrane', 'CarryDeckCrane', 'CrawlerCrane' and 'RingCrane'. If I have a
&= gt; 'Crane' key, how do I determine which of the child tables the key belon= gs to
> without querying each child table?
>
> I greatly= appreciate any effort to help me. Thank you for your time and
> cons= ideration.
>
> Sincerely,
> Tricsta
> --
> = View this message in context: http://www.nabble.com/Determine-which-child-t= able-a-record-belongs-to-tp15164998p15164998.html
> Sent from the Apa= che Derby Users mailing list archive at Nabble.com.
>

= --_a4a90b4d-fd45-4c46-b002-c354fc53e369_--