Arek Kasprzyk wrote:
It is certainly possible to combine multiple source schemas into a single
biomart schema. You can either do it manually using your scripts or
automatically using Mbuilder. The logic will be the same.(for an example
look at ensembl Mbuilder config files) I am not sure what the problem with
unique keys is as they tend to identify different things and should not
clash. Perhaps if you could send us a bit more detail on how these schemas
are structured and how the resulting biomart schema looks like we would be
able to help you more.
I have 'subject' table with multiple phenotype tables which all have
foreing key link to the subject table. Subject table has
subject_id (PK)
birth_date
gender
time_stamp
...
For example of phenotype tables, I have 'weight' with
subject_id (FK)
weight_kg
date_measured
...
We have 7 different database instances located on different sites. Data
is different but database schemas are same but not all things are
measured in each so they might lack some phenotype tables or have no
data in them. However since these are all their own instances, their
subject_id values are not unique over all the sites. They are unique
within their own site. When I combined the data to create one biomart
instance, I replaced the subject_id columns for all table to a running
number with the script which combined them.
For the mart the 'subject' table would be main table and phenotype
tables dm tables. I would like to get one subject table from all N
locations subject tables. Same applies to the phenotype tables so that
there would be only one weight table (and not N weight tables).
As far as views are concerned - this is possible assuming that the
conversion logic is not too complex. However I would advise you to use
materialized schemas as otherwise you would loosing the benefits of query
optimizations
I did assume that it would be slower to access.
Regards,
--
Henrikki Almusa
