Re: Q: data modeling with inheritance

On Thu, Jul 02, 2009 at 01:54:04PM -0700, Reece Hart wrote:
> This is a question about data modeling with inheritance and a way to
> circumvent the limitation that primary keys are not inherited.

I'm missing what you're doing here that foreign keys don't cover.
Could you send along your DDL?

Just generally, I've only found table inheritance useful for
partitioning. "Polymorphic" foreign key constraints can be handled
other ways such as the one sketched out below.

http://archives.postgresql.org/sfpug/2005-04/msg00022.php

Cheers,
David.

>
> I'm considering a project to model genomic variants and their associated
> phenotypes. (Phenotype is a description of the observable trait, such as
> disease or hair color.) There are many types of variation, many types of
> phenotypes, and many types of association. By "type", I mean that they
> have distinct structure (column names and inter-row dependencies). The
> abstract relations might look like this:
>
> 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
>
>
> There are several types of variants, such as insertions, deletions,
> inversions, copy-number variants, single nucleotide polymorphisms,
> translocations, and unknowable future genomic shenanigans.
>
> Phenotypes might come from ontologies or controlled vocabularies that
> need a graph structure, others domains might be free text. Each is
> probably best-served by a subclass table.
>
> Associations might be quantitative or qualitative, and would come from
> multiple origins.
>
> 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).
>
> So, how would you model this data? Do I ditch inheritance?
>
> Thanks,
> Reece
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate