On 13-Mar-08, at 10:29 AM, Alex wrote:
In fact, i wanted to have dms associated to cohort__main and
origin__main (and sample_main). The complete structure would be:
CREATE TABLE hsapiens_testconf__sample__main (
sample_id_key int(10) default NULL,
KEY (sample_id_key)
);
CREATE TABLE hsapiens_testconf__cohort__main (
sample_id_key int(10) unsigned,
cohort_id_key int(10) unsigned,
KEY (cohort_id_key),
KEY (sample_id_key)
);
CREATE TABLE hsapiens_testconf__origin__main (
sample_id_key int(10) unsigned,
origin_id_key int(10) unsigned,
KEY (origin_id_key),
KEY (sample_id_key)
);
Hi Alex,
the above three mains are not going to work because they do not follow
the 'key inheritance' principle.
1. You need to decide which of the two mains you want to have
sample and cohort
or
sample and origin
the reminder (either cohort or origin) becoming a dimension of of a
sample
OR
2. either cohort or origin needs to have to the other one's key ei
cohort (sample_id, cohort_id, origin_id)
or
origin (sample_id, origin_id, cohort_id)
only then they may become three mains. (not sure if this is possible
in your data model though)
OR
3. you can have two datasets
one consisting of mains: sample and cohort and the other of sample and
origin
(probably the worst option for the user)
the bottom line is that in order to have n number of mains they need
to have n-1 number of foreign keys linking them sequentially.
the rest of the tables below (dms) are perfectly fine. My personal
favourite would be option (1). and choose second main as the one you
most
likely to wanting to filter on while the other becomes a dimension of
a sample. Why don't you try one of the two option, configure the system
and see if this works for you. If not you can always try to do it vice
versa later on
hope this helps,
a.
CREATE TABLE hsapiens_testconf__cohortdesc__dm (
cohort_id_key int(10) unsigned,
description_coh varchar(40) default NULL,
KEY (cohort_id_key)
);
CREATE TABLE hsapiens_testconf__origindesc__dm (
origin_id_key int(10) unsigned,
description_orig varchar(40) default NULL,
KEY (origin_id_key)
);
CREATE TABLE hsapiens_testconf__sampledesc__dm (
sample_id_key int(10) default NULL,
individual_name varchar(40) default NULL,
KEY (sample_id_key)
);
Would there be any work around to have this within the same dataset
OR having this in multiple datasets without having the user
selecting those datasets (ie, the linking in between the datasets
would be done in background)?
Thanks again,
Alex
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Arek Kasprzyk
Sent: March 12, 2008 4:36 PM
To: Alex
Cc: [email protected]
Subject: Re: [mart-dev] Multiple subclassed tables associated to a
main within a dataset
On 12-Mar-08, at 3:40 PM, Alex wrote:
Hello,
Hi Alex,
I am wondering if it is possible to have more than one subclass
(main) table associated to a single central main table within the
dataset but not associated amongst themselves.
Here is a sample schema:
CREATE TABLE hsapiens_testconf__sample__main (
sample_id_key int(10) default NULL,
KEY (sample_id_key)
);
CREATE TABLE hsapiens_testconf__cohort__main (
sample_id_key int(10) unsigned,
cohort_id_key int(10) unsigned,
KEY (cohort_id_key),
KEY (sample_id_key)
);
CREATE TABLE hsapiens_testconf__origin__main (
sample_id_key int(10) unsigned,
origin_id_key int(10) unsigned,
KEY (origin_id_key),
KEY (sample_id_key)
);
Thanks,
Alex
the main tables form a chain of 1: m relationships and each of the
subclasses has a foreign key from all the superclass tables. Based
on the defined keys in your talbes
i can see two possible combinatons of mains and one dm:
sample and cohort mains + origin dm
or
sample and origin mains and cohort dm
(for dms you only need one key)
a.
