Re: Q: data modeling with inheritance

>
>  variant              association                phenotype
>  -------              -----------                ---------
>  variant_id --------- variant_id        +------- phenotype_id
>  genome_id            phenotype_id -----+        short_descr
>  strand               origin_id (i.e., who)      long_descr
>  start_coord          ts (timestamp)
>  stop_coord
>

Is an association, for example, an experiment that establishes a
dependent relationship? So could there be multiple associations
between variant and phenotype?

> The problem that arises is the combinatorial nature of the schema design
> coupled with the lack of inherited primary keys.  In the current state
> of PG, one must (I think) make joining tables (association subclasses)
> for every combination of referenced foreign keys (variant and phenotype
> subclasses).
>

Is your concern that the number of joins will grow exponentially in
the number of variants and phenotypes?

> So, how would you model this data?  Do I ditch inheritance?

I've put some thought into representing an ontology via table
inheritance, and I've never been able to figure out a good way ( of
course, that's not to say one doesn't exist... ) .

If I understand your problem correctly, I would use composite keys (
ie ( variant type, id ) ) and then use an extra join to separate
ontology tables to restrict searches to specific branches. So all
variants would be stored in the variants table, all phenotypes are in
the phenotypes table, and you join through association. It's not as
elegant as inheritance, but it will be much more flexible in both the
types of queries that you can write and in case your requirements
change.

-Nathan