You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by MZ_TRICSTA <mz...@yahoo.com> on 2008/01/29 18:46:09 UTC
Determine which child table a record belongs to
Hello All,
I have the following tables in my database -:
CREATE TABLE Crane
(
PTTEquipmentID INTEGER NOT NULL,
LiftingCapacity INTEGER NOT NULL,
PRIMARY KEY (PTTEquipmentID),
FOREIGN KEY (PTTEquipmentID) REFERENCES Equipment ON DELETE CASCADE
)
CREATE TABLE RoughTerrainCrane
(
PTTEquipmentID INTEGER NOT NULL,
PRIMARY KEY (PTTEquipmentID),
FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
)
CREATE TABLE AllTerrainCrane
(
PTTEquipmentID INTEGER NOT NULL,
PRIMARY KEY (PTTEquipmentID),
FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
)
CREATE TABLE TruckCrane
(
PTTEquipmentID INTEGER NOT NULL,
PRIMARY KEY (PTTEquipmentID),
FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
)
CREATE TABLE CarryDeckCrane
(
PTTEquipmentID 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 NULL,
PRIMARY KEY (PTTEquipmentID),
FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
)
'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?
I greatly appreciate any effort to help me. Thank you for your time and
consideration.
Sincerely,
Tricsta
--
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.
Re: Determine which child table a record belongs to
Posted by Daniel Noll <da...@nuix.com>.
On Wednesday 30 January 2008 05:28:14 Craig L Russell wrote:
> The alternative is to include all possible tables in a query, which is
> performance-limiting.
Another alternative is to use single table inheritance, where you still have a
type column but also put every field from every class into the one table and
just don't fill out the ones which don't make sense for a given type. Rails
developers are very fond of this strategy.
Daniel
Re: Determine which child table a record belongs to
Posted by Craig L Russell <Cr...@Sun.COM>.
On Jan 29, 2008, at 10:49 AM, MZ_TRICSTA wrote:
>
> One last question, guys. What if there is overlapping between the
> child
> tables, so that one instance has more than one "Type"?
Then you might want to model this as an Entity-Role relationship which
allows multiple rows in associated tables for the same row in the
primary table.
If this is the case, then you can't use a single-valued discriminator.
You could use a bit field represented as a number in which each bit
corresponds to an associated Role with data in another table.
Craig
>
>
>
> Craig L Russell wrote:
>>
>> This is a very common pattern in object-relational mapping. The
>> column
>> that contains the type of crane is commonly referred to as a
>> discriminator column, and is commonly a CHAR, VARCHAR, or NUMERIC
>> type. The value of the discriminator column that identifies which
>> table to look for is referred to as a discriminator value.
>>
>> The alternative is to include all possible tables in a query, which
>> is
>> performance-limiting.
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/
>> jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>>
> --
> View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15166419.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Determine which child table a record belongs to
Posted by MZ_TRICSTA <mz...@yahoo.com>.
One last question, guys. What if there is overlapping between the child
tables, so that one instance has more than one "Type"?
Craig L Russell wrote:
>
> This is a very common pattern in object-relational mapping. The column
> that contains the type of crane is commonly referred to as a
> discriminator column, and is commonly a CHAR, VARCHAR, or NUMERIC
> type. The value of the discriminator column that identifies which
> table to look for is referred to as a discriminator value.
>
> The alternative is to include all possible tables in a query, which is
> performance-limiting.
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
--
View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15166419.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Determine which child table a record belongs to
Posted by MZ_TRICSTA <mz...@yahoo.com>.
Thanks, Craig. Glad to know that this is common practice. I am in the process
of implementing the solution as we speak.
Craig L Russell wrote:
>
> This is a very common pattern in object-relational mapping. The column
> that contains the type of crane is commonly referred to as a
> discriminator column, and is commonly a CHAR, VARCHAR, or NUMERIC
> type. The value of the discriminator column that identifies which
> table to look for is referred to as a discriminator value.
>
> The alternative is to include all possible tables in a query, which is
> performance-limiting.
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
--
View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15166310.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Determine which child table a record belongs to
Posted by Craig L Russell <Cr...@Sun.COM>.
This is a very common pattern in object-relational mapping. The column
that contains the type of crane is commonly referred to as a
discriminator column, and is commonly a CHAR, VARCHAR, or NUMERIC
type. The value of the discriminator column that identifies which
table to look for is referred to as a discriminator value.
The alternative is to include all possible tables in a query, which is
performance-limiting.
Craig
On Jan 29, 2008, at 10:16 AM, MZ_TRICSTA wrote:
>
> Oh! I see. So I would need to add a "Type" column for all my parent
> tables.
> That would definitely make things easier, but I was hoping there was
> some
> SQL code that would magically give me the name of the child table.
> Still,
> this will work fine. Thanks for all your help. ~Tricsta
>
>
> Brad Moore wrote:
>>
>> No problem. Glad to be of help.
>>
>> If you have a CraneType column in the master table then you could use
>> that to determine which other table has associated data for that
>> Crane. i.e. if CraneType = "ROUGHTERRAIN" then look in the
>> RoughTerrainCrane table.
>>
>> Brad
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15165775.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
RE: Determine which child table a record belongs to
Posted by MZ_TRICSTA <mz...@yahoo.com>.
Oh! I see. So I would need to add a "Type" column for all my parent tables.
That would definitely make things easier, but I was hoping there was some
SQL code that would magically give me the name of the child table. Still,
this will work fine. Thanks for all your help. ~Tricsta
Brad Moore wrote:
>
> No problem. Glad to be of help.
>
> If you have a CraneType column in the master table then you could use
> that to determine which other table has associated data for that
> Crane. i.e. if CraneType = "ROUGHTERRAIN" then look in the
> RoughTerrainCrane table.
>
> Brad
>
>
--
View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15165775.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
RE: Determine which child table a record belongs to
Posted by Brad Moore <br...@hotmail.com>.
No problem. Glad to be of help.
If you have a CraneType column in the master table then you could use
that to determine which other table has associated data for that
Crane. i.e. if CraneType = "ROUGHTERRAIN" then look in the RoughTerrainCrane table.
Brad
> Date: Tue, 29 Jan 2008 10:04:26 -0800
> From: mz_tricsta@yahoo.com
> To: derby-user@db.apache.org
> Subject: RE: Determine which child table a record belongs to
>
>
> Thanks for your help, Brad. You are absolutely right. In this case, it
> doesn't make sense to have so many tables. I will make the changes you
> suggest. However, there are times when it does make sense to have this kind
> of hierarchical structure such as when the child tables have different
> fields, so my question remains: How would I determine which of the child
> tables the key belongs to without querying each child table? I appreciate
> any further assistance.
>
> Again Thanks,
> Tricsta
>
>
> Brad Moore wrote:
> >
> > Can I suggest changing 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 tables to determine the type of crane, the same thing could be
> > done with one additional column in the Crane table. That would simplify
> > your database design a lot. Unless I'm missing something.
> >
> > Brad
> >
> >
>
> --
> View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15165402.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
_________________________________________________________________
RE: Determine which child table a record belongs to
Posted by MZ_TRICSTA <mz...@yahoo.com>.
Thanks for your help, Brad. You are absolutely right. In this case, it
doesn't make sense to have so many tables. I will make the changes you
suggest. However, there are times when it does make sense to have this kind
of hierarchical structure such as when the child tables have different
fields, so my question remains: How would I determine which of the child
tables the key belongs to without querying each child table? I appreciate
any further assistance.
Again Thanks,
Tricsta
Brad Moore wrote:
>
> Can I suggest changing 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 tables to determine the type of crane, the same thing could be
> done with one additional column in the Crane table. That would simplify
> your database design a lot. Unless I'm missing something.
>
> Brad
>
>
--
View this message in context: http://www.nabble.com/Determine-which-child-table-a-record-belongs-to-tp15164998p15165402.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
RE: Determine which child table a record belongs to
Posted by Brad Moore <br...@hotmail.com>.
Can I suggest changing 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 tables to determine the type of crane, the same thing could be done with one additional column in the Crane table. That would simplify your database 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
>
>
> Hello All,
>
> I have the following tables in my database -:
>
> CREATE TABLE Crane
> (
> PTTEquipmentID INTEGER NOT NULL,
> LiftingCapacity INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Equipment ON DELETE CASCADE
> )
>
> CREATE TABLE RoughTerrainCrane
> (
> PTTEquipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )
>
> CREATE TABLE AllTerrainCrane
> (
> PTTEquipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )
>
> CREATE TABLE TruckCrane
> (
> PTTEquipmentID INTEGER NOT NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )
>
> CREATE TABLE CarryDeckCrane
> (
> PTTEquipmentID 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 NULL,
> PRIMARY KEY (PTTEquipmentID),
> FOREIGN KEY (PTTEquipmentID) REFERENCES Crane ON DELETE CASCADE
> )
>
> '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?
>
> I greatly appreciate any effort to help me. Thank you for your time and
> consideration.
>
> Sincerely,
> Tricsta
> --
> 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.
>
_________________________________________________________________