> > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
