I run a database for a sequencing centre. There are other developers getting data out of this and running automated and semi automated processes from the database.
We are discussing a change to the database.
At the moment we have a library that has a one-to-one relationship with a multiplex_index, which can be used to identify the library. We now want to add an optional GBS_index to each library (another optional one-to-one relationship). So each library will have a multiplex_index and optionally a GBS_index.
The columns stored on the new GBS_index will be the same as for the multiplex_index. So we have 2 options for storing the GBS_index data.
1) Store the GBS_index data in the original multiplex_index table, and identifying it by its “type” field.
2) We can add a parallel table for the GBS_index, which mirrors the multiplex_index table.
I favour the second parallel table option, as I see it as less likely to break existing code (scripts query the database for a list of multiplex_indexes, these would need to exclude the GBL_indexes in this case).
Conceptually the two types of indexes are both “multiplex indexes”, but used in a slightly different manner.
Are there any strong arguments for or against either option?
Ok, a quick explanation of DNA sequencing may help.
DNA sequencing involves taking DNA from cells and splitting them up into “reads” of approx 200 base pairs (each base pair is like a DNA char A,T,G or C).
We load libraries (DNA from a prepared biological sample) on to a machine and sequence them. The two types indexes I talked about are a another piece of DNA with a known sequence, that is attached to the start of the library DNA before it is sequenced. That way, we can read the first bit of sequence we can identify what library each read (~200 chars) came from. (We are generally interested in the numbers of reads matching a position on a reference genome).
Now we can have one (multiplex) or two (multiplex + GBS) indexes at the start of the library DNA (one comes after the other), which form a unique combination to identify the library.
Initially two libraries with the same multiplex_index should not be loaded together, as you couldn’t identify them from the multiplex_index sequence. Now the combination of the two indexes should be unique.
5
Parrallel Table:
You are correct that using a new “parallel” table for GBS_index will be less likely to break existing processes. Even if you could reuse the old table without an extra type-field you would be changing the relationship from a 1-1 to a 1-many. This would be a breaking change.
The draw back is this design is not data driven. What if a 3rd type of index is introduced? 4th, 5th…. 20th? I’m not familiar with DNA but if the types can scale it would require a query change every time a new type is introduced. Adding a type with a parallel table may not be a “breaking” change but it would require a change nonetheless every time a type is introduced. Writing queries against 20+ tables would be cumbersome.
Add an extra type field:
This design would be a breaking change. You are changing the relationship from 1-1 to 1-many. But if the relationship really is 1-many then it “should” be modeled that way in the database.
This design is also data driven. Every time a new type is introduced you merely add data to the “type” lookup table. No query changes required (except for the initial change you are doing now of course).
Conclusion:
I prefer adding the extra field as it models the 1-many relation with a 1-many relationship in the database. The parallel table emulates a 1-many with multipe 1-1 relations and is not data-driven for new types in the future.
Both designs will work. It comes down to how critical and political a breaking change is. And whether the types are fixed at 2 or scale to N.
7