I have raised a question on dba.stackexchange.com but as the heart of my question is in fact to do with logic I have raised a more general representation of my issue here. I think it is the right place, apologies if not.
I have a number of objects that are read and written to in a set sequence. I also have four locations in which I can store these objects. These objects vary in size but for the purposes of simplicity I have rated them 1 to 3 with 3 being the largest. Location of objects is fixed e.g. objects can not be moved in between sequence steps
I have a table which details the sequence of the process. It has three columns: Sequence, Source, Destination. Processes can and do run in parallel and therefore sequence is not a unique identifier however should one be required it can be considered to be in place.
In an ideal world no object should be located in a way that would result in no object required for a sequence step being co-located with another. Of course this is highly unlikely and instead I am looking for a best solution as opposed to a perfect one.
I have therefore considered that the potential locations may be best ranked as followed.
- All objects for a sequence located separately.
- Source and destination objects for sequence located separately.
- Objects located anywhere
Given that each object has a size I also considered perhaps multiplying the location rank by the size and therefore giving the locations a score. Then based on this I could find the best solution by looking at it's total score.
Does anyone have any suggestions or perhaps addressed a similar problem in the past?
Any help input is greatly appreciated.