inheritance

[Geo]

I have one supper class table called sample and some subclasses called DNA ,
RNA etc. which inherite from Sample table. In sample table I have SampleId
(PK) and sampletype to show whether its RNA or DNA etc. But if i have to
query on the basis of sampleID to get all information about that, then How
can i know in which subtable it is? In my case i have to join all subclass in
all case to get the ans.

Any other option like a subquery which gives the sampletype from sample
table by taking sampleID and then i should forward the main query to that
particular subtable?

 

[Stefan Hoffmann]

hi Geo,

I have one supper class table called sample and some subclasses called DNA ,
RNA etc. which inherite from Sample table.

Super and sub classes in databases theory may give you this table layout:

Sample: ID, Discriminator, common fields

with e.g. Discriminator Text(3) containing either DNA or RNA.

Sample_DNA: idSample, DNA specific fields
Sample_RNA: idSample, RNA specific fields

One problem with super and sub classes is that you cannot ensure
referential integrity on declarative level only. Normally you would use
a trigger/stored procedure solution the enforce RI.

In sample table I have SampleId
(PK) and sampletype to show whether its RNA or DNA etc. But if i have to
query on the basis of sampleID to get all information about that, then How
can i know in which subtable it is?

You normally would use an extra discriminator in the table derived from
the super class.

In my case i have to join all subclass in all case to get the ans.

In Access you could use DCount() to get an updateable query:

SELECT *,
DCount("*", "Sample_DNA", "idSample = " & ID) AS isDNA,
DCount("*", "Sample_RNA", "idSample = " & ID) AS isRNA
FROM Sample

For performance reasons you should use a discriminator.


btw, you may have a look at the EAV model:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

depending on the field types and their distribution between super and
sub classes this may be also a solution.


mfG
--> stefan <--