The problem presented me is to store a "profile" that relates to ordered "steps". One profile has many steps. Obviously, it is no problem to relate these steps to the corresponding profile via a foreign key column. However, preserving the order of the steps is trickier because steps can be inserted, deleted, and/or rearranged. What I am looking for is a cleaner way of doing this than having an order_by column of some sort on steps because, for example, inserting a step between 1 and 2 in a 20-step process would involve multiple cascading updates of all of the steps 2-20.
Storing the relationship on the profile table as an array of foreign keys would be ideal but I've found nothing to indicate that this is possible in any legitimately relational way. The best I've come up with is to store the many to one relationship on the steps table and have a column on profile with a stringified array that is replaced wholesale when updates to the ordering are made. I fear though that this "hardcoded" non-relational method is too susceptible changes in the steps table that could go "unnoticed" by the array particularly when there are multiple database environments.