I’m trying to save some items in a database but am a bit flumoxed on the how.
I deal with several Items (“proteases”) which each have multiple replacement rules. The order in which these rules are executed is important, eg “add a space after all K except if K is followed by P” (to use a simple one)
Replace “K” with "K "
Replace “K P” with “KP”
How would I save this in a database while keeping the order for each item? I’m somehow under the impression that the order isn’t fix (for
You would need an additional column for a formula or a copy of your original column where you make your replacements. Then you do an order by the original column.
Thanks, but I might not have expressed it well enough.
I have some items which are stored in a table PROTEASES
I have a table RULES which contains two columns (Target and Replacement) with the replacement rules.
“K” "K " <- ID1
“K P” “KP” <- ID2
“R” "R " <- ID3
“R P” “RP” <- ID4
“K K” “KK” <- ID5
“R R” “RR” <- ID6
Each protease can have multiple rules, each rule can occur in multiple Proteases, so we have an n:n relationship and need to use a link table PROTEASE-RULES
Now lets say (don’t look at the actual content) for Trypsin the rules are (in this order) 1,2,3,4,5,6
but for Chymotrypsin they are 3,4,1,2
and for Elastase they are 3,1, 8
I know I can get the rules for each protease from the link table but where do I store the order information? I could store it as a string in PROTEASES and parse it but that seems inelegant. I’m sure there is a better solution somewhere
If I understand you correctly, it looks like you can just add a SortOrder column to PROTEASE-RULES. Then when you need to know the rules, you fetch them in sorted order:
SELECT r.Target, r.Replacement FROM PROTEASE-RULES pr
INNER JOIN PROTEASES p ON pr.PROTEASE_ID = p.PROTEASE_ID
INNER JOIN RULES r ON pr.RULES_ID = r.RULES_ID
WHERE p.Name = "Trypsin" ORDER BY pr.SortOrder
Thanks Paul - I just came up with that too. Silly me, but sometimes it just helps laying out the problem for others to find the solution.
Looking forward to the webinar tonight