Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
It is very hard to get one's head around this question purely in the abstract. Can you explain what these tables are actually being used for? –  dan1111 Sep 20 '13 at 10:43
    
What are your goals then? Ease of use? Simple design? HIgh read performance for aggregation (requires full table scans)? High read performance for random access of single rows? High write performance? Simple recovery scenarios? –  knb Sep 20 '13 at 11:07
    
I wanted to learn some opinions on what was the better design. In this case, performance is unlikely to be an issue, as both tables will contain a few hundred rows, and get updated infrequently. I Guess ease of use / simple design is a bigger problem. –  wobbily_col Sep 20 '13 at 11:14
    
An example of a "typical" query might help in seeing the "better" schema. You're basically wanting to know if it would be better to relate a row on tableA to another row on tableB using a column specifying the related id from tableB or a relate table (lookup table) that has the ids of both rows? Is that right? –  Anthony Sep 20 '13 at 11:27
    
OK, I will simplify it. Imagine a database that needs to contain "work" addresses and "home" addresses. Do you store these in the same table with an address type, or a separate table for each. You are only likely to download all the work addresses in one query, or all the home addresses in one query. Columns will be the same for each table. –  wobbily_col Sep 20 '13 at 11:30

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
1  
Aren't all the relationships in this question 1-1? –  JeffO Sep 20 '13 at 12:50
    
@JeffO. Nope. His base table will have up to 2 indexes making it a 1-many. He is thinking about emulating a 1-many using two 1-1 relations to avoid breaking code. –  mike30 Sep 20 '13 at 12:53
    
It depend how you look at it as a 1-1 or a 1-to-many. The new indexes are multiplex indexes, but used in a different context, therefore you could equally look at it as 2 1-1 relationships. For the time being two 1-1 indexes seems to model the situation best, but the technology and way they use it changes quickly. There is a chance that they will be more type later, but unlikely to be a lot more. –  wobbily_col Sep 20 '13 at 13:06
    
If I draw (what I consider) an accurate ER diagram, it isn't actually a 1-many, but two 1-1 relationships (one is optional, the other is not), that store the same data. Does that change your conclusion? –  wobbily_col Sep 20 '13 at 13:17
1  
@wobbily_col. It's hard to say if I'd change the conclusion since I'm not familiar with the DNA you are modeling. Whether a type is optional is a separate issue from the relation type. For example if a home Address is required, but a vacation home is optional it doesn't make Addresses a 1-1 relation to Persons. You could choose to physically model multiple addresses with multiple 1-1 relations but the logical relation is 1-many. –  mike30 Sep 20 '13 at 18:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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