Cookie Notice

As far as I know, and as far as I remember, nothing in this page does anything with Cookies.

2010/08/06

Poor Planning Prevents Proper Performance: An SQL Tale

I'm making an application that handles workflow for a gene sequencer. What's on my plate right now is creating a record online that we can then feed into the machine. The process starts with a request, and the names of all the tables related to the request match /^request/. Each request has one or more accessions (you can think of them as samples), and the name of each table related to accession matches match /^accession/. Not everything involved with this is perfect but not bad for a project that I wrote everything for.

But we don't handle requests. We don't load requests into the sequencer. We put samples on a chip and run the process on that. A chip can have 1-n regions with 1-n samples in each region, using barcoding, which I'll have to explain sometime.

What's the term for this? What did I use? "Chip"? "Run"?

Sequence.

And for a unique sample used in the run?

Barcode.

Let me metaphor for a moment to explain why this is so hateful. Imagine you're an official for Imaginary Elementary School, about to take the 2nd grade class to the Zoo for a field trip. Problem is, the 2nd grade class of Theoretical Elementary is going to. As is the 2nd grade of Potential Elementary, etc. So, you get a bunch of bright yellow shirts in 2nd-grader sizes that say "Imaginary Elementary Class Trip 2010". Then Theoretical gets a bunch of bright blue shirts saying "Theoretical Elem", etc., and now you know if that group of eight-year-olds running around is Imaginary or Theoretical.

Now, instead of 2nd graders, we're talking DNA samples, and instead of t-shirts, we have chemicals. When I think "barcoding", I think of putting barcodes on products so I know what price they are. I think "tagging" (like they do with wild animals to track their movements) is a more fitting metaphor and I like my extended T-shirt thing. And, right now, I'm wanting to make a table where I can say that, on August 6, the Imaginary Elementary kids wore red, and the obvious table name would be sequence_barcodes and the functions I need to write should be get_sequence_barcode() and set_sequence_barcode(), but because I'm using "barcode" as the generic term for the 2nd-grader and not the shirt the child wears, that table name and those function names are already used.

So I can't use the right names now because the wrong names were used in the past. I might just call the table keaton.