Search Postgresql Archives

Re: data modeling genes and alleles... help!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mar 30, 2013, at 3:38, Modulok <modulok@xxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux