Ordered items in database?

Hi,

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 … each etc).

Thanks

Markus

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

Trypsin
HS-Trypsin
Chymotrypsin
Elastase

etc.

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
… etc

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

PROTEASE_ID RULES_ID

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 :slight_smile: