1
\$\begingroup\$

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?

\$\endgroup\$
1
\$\begingroup\$

Is this a valid approach?

FROM tissue_source_site, ampl_cols, mrna_cols

What you are doing here is to get a product of those tables, meaning that if the RDBMS is not wise it may attempt to create an intermediate structure with size = product of the sizes of each table.

Gladly I just got a query plan execution for SQL Server and they can infer the same plan for either your where clauses, and the join operator, meaning that they are just as efficient (but I would prefer use a join anyway).

Is there a point at which I will hit a wall, concerning the length of the queries (endless ANDs) or performance-wise?

You shouldn't be too concerned about the length of your queries. Querying, after all, is the way to get your data. So you should let your query express what you want to retrieve.

Performance wise you should get a query plan as I did for SQLServer so you can choose between a join and a where. Doing many joins can also hinder performance but most of times this isn't an issue because they are done over the primary key of the tables which most of the times are indexed (you can see indexing as a way to arrange data so it's easier to retrieve it by that index)

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?

There are many types of joins and someday you will probably be wanting the functionality of one of them, having the wisdom of picking the right tool for the job is always nice.

Or WITH table AS? Or something else I am totally unaware of?

Your query is still pretty simple and doesn't require any "black magic". try to keep things simple and give yourself time to understand and get used to the sql querying concept.

\$\endgroup\$
  • \$\begingroup\$ I have to read more carefully the SQLite documentation, but looking at it now, it indeed might be that a NATURAL JOIN is much better than what I am doing there. \$\endgroup\$ – XXX Nov 6 '15 at 20:33
  • \$\begingroup\$ @Boris If I'm not mistaken Natural join does the equivalent of inner join with the primary key which is you are attempting to do. You are on the right path. \$\endgroup\$ – Bruno Costa Nov 6 '15 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.