This is a part of a project I am working on. I need advice and criticism regarding the data base design and general approach to querying the data. I am not at all experienced with relational data bases (as you should notice), and even though I have tried to invest some time in reading up I am currently forced to just move along as fast as possible.
Currently using SQLite3, and most probably sticking with it.
The data base schema goes along the lines of:
-- gene ids and protein names CREATE TABLE ensemblid ( geneid TEXT NOT NULL PRIMARY KEY, description TEXT, -- not used so far name TEXT NOT NULL ); -- tss id and site and study names CREATE TABLE tissue_source_site ( tss TEXT PRIMARY KEY, source_site TEXT NOT NULL, study_name TEXT NOT NULL, bcr TEXT NOT NULL -- not used so far ); -- sample type id and its human-readable definition CREATE TABLE sample_type ( sample_type TEXT PRIMARY KEY, definition TEXT NOT NULL, short_letter_code TEXT NOT NULL -- not used so far ); -- analyte id and its human-readable definition CREATE TABLE portion_analyte ( analyte TEXT PRIMARY KEY, definition TEXT NOT NULL ); -- tables containing indices into two LARGE data matrices of REALs -- that are currently **not** in the relational database CREATE TABLE mrna_rows ( rowindex INTEGER PRIMARY KEY, geneid TEXT NOT NULL ); CREATE TABLE ampl_rows ( rowindex INTEGER PRIMARY KEY, geneid TEXT NOT NULL ); CREATE TABLE ampl_cols ( rowindex INTEGER PRIMARY KEY, tss TEXT NOT NULL, patient TEXT NOT NULL ); CREATE TABLE mrna_cols ( rowindex INTEGER PRIMARY KEY, tss TEXT NOT NULL, patient TEXT NOT NULL, sample_type TEXT NOT NULL, vial TEXT NOT NULL, portion TEXT NOT NULL, analyte TEXT NOT NULL );
First off, I do realize that I have not put any effort at that moment to ensure data integrity (apart from the obvious
PRIMARY KEY and
NOT NULL). There are a few foreign keys lurking in there: for example, the
tss column in both
mrna_cols is the same
tss that is the primary key of the
tissue_source_site table. Another issue is that the
patient column, in every table it is used, is a unique identifier consistent across all tables. The data, as I have it, contains no further information about "patients", so I have not felt the need to make it a unique column in a table of its own.
After some trial and error, and much unsuccessful mucking around with the different
JOINs, I eventually got to the schema above, and the following "style" of querying it:
Get the names of sites and studies, along with number of patients present in both data sets.
SELECT tissue_source_site.source_site, tissue_source_site.study_name, COUNT(1) FROM tissue_source_site, ampl_cols, mrna_cols WHERE ampl_cols.patient == mrna_cols.patient AND tissue_source_site.tss == ampl_cols.tss AND tissue_source_site.tss == mrna_cols.tss GROUP BY tissue_source_site.tss;
Which gives me the expected results:
UCSF|Breast invasive carcinoma|8 Walter Reed|Breast invasive carcinoma|65 -- and so on
Is this a valid approach? Is there a point at which I will hit a wall, concerning the length of the queries (endless
ANDs) or performance-wise? How about this
table.column way of doing everything: I did not really see this used much the tutorials I did (in a hurry). What are the drawbacks of doing it like that? Is it wiser to figure out how
JOIN works? Or
WITH table AS? Or something else I am totally unaware of?