On Mar 30, 2013, at 3:38, Modulok <modu...@gmail.com> wrote: > List, > > I have a data modeling problem. That much, I know. The question is how do I > model this? (Below.) > > I'm making a database which will store pseudo-genetic data. It's a basic > many-to-many setup:: > > create table person( > name varchar(32) primary key > ); > create table gene( > name varchar(32) primary key > ); > create table person_gene( > person varchar(32) references person(name), > gene varchar(32) references gene(name) > );
If my vague memories of high-school biology are right, then I think you might be on the wrong track here. > Great. This is important as I need to be able to ask questions like "who > carries gene 'x'?" as well as "what genes does person 'y' carry?" But then > things get thorny... > > I also need to store the properties of the individual genes (the alleles). > This > is akin to an instance of one of the many gene classes in my application code. > So I make more tables:: > > create table hair( > id serial primary key, > density float, > thickness float > ); > create table eye( > id serial primary key, > pupil_type int > ); > > How do I store a reference to this data? I'd add a column to the person_gene > table, but it points to what? I can't reference a column name because they're > all stored in different tables. I also can't store them in the same table, as > they all store different data. Do I store the *table name* itself in a column > of the gene_table? (Smells like a klude.) Aren't alleles related to genes? (Hence my previous remark) If that's the case, you'd add a gene foreign key to each allele table and then store which alleles are related to a person instead of which genes are related to a person. The genes then follow from the alleles. Is that closer to what you're after? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
