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 ampl_cols and 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?
