From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Modulok <modulok(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: data modeling genes and alleles... help! |
Date: | 2013-03-30 11:16:17 |
Message-ID: | C6C8649E-D94F-4CA3-A6FB-5A29FB62AF79@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mar 30, 2013, at 3:38, Modulok <modulok(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Misa Simic | 2013-03-30 11:39:52 | Re: Money casting too liberal? |
Previous Message | Misa Simic | 2013-03-30 10:55:24 | Re: data modeling genes and alleles... help! |