Hi, I am trying to model a Process / Service Registry for a batch application, with the following entities ( see attached JPG ) :

PROCESS - A job
PROCESS PARAMS - The parameters that are expected to be passed into that job ( no actual values, just the definitions )
SERVICE - The instantiation of that job ( not for each time the job is run, but the number of different ways it can be run )
SERVICE PARAMS - Should contain the exact same set of parameters as Process Params, but contain the values to be used in that instantiation

The problem I have is, that a Service instantiates a single job, and a job will have many parameters defined, but when I model the instantiation of parameters for that service, based on the process parameters, I have a closed loop of relationships, meaning that in my SERVICE PARAMS table, I can effectively use two different joins to get the "process_id"... Therefore I have a redundant link to the process via SERVICE_PARAM > PROCESS_PARAM, so if I happen to change say the PROCESS which the SERVICE instantiates, then the SERVICE PARAMS are no longer valid, but still link to the service.. How should I model this so there is no overlapping / redundancy of process, seeing Im am effectively linking to process twice ? Or should this just be an application side thing to ensure they stay in sync ( ie. delete all SERVICE PARAMS, if PROCESS_ID changes in SERVICE ) ?

Thanks in advance,
Adam

It is perfectly valid. What you want is a rule to ensure that the Service Parameters are associated with Process Parameters from the same Process as the Service.

Some DBMSs may allow you to create a complex constraint to check this - something like:

CHECK (NOT EXISTS(SELECT NULL FROM ... WHERE ... AND p.process_id != s.process_id));

Otherwise, probably the easiest way is to cascade the process_id into the child tables so that table Service Parameters has it twice, e.g. service_process_id and parameter_process_id. Then all you need is a simple check constraint to ensure they are the same.

You will proably also want application (preferably server-side) code to make it easy for users to move a service to another process, if that is something they are wont to do.

The terms you use to describe your entities hint at another possible solution: instead of allowing the application to change service's parent process instantiate a new service, with its own set of parameters.

After all, if you've got an apple (an instance of fruit) you can't make an orange (another instance of fruit) out of it; you'll throw an apple away and get yourself a brand new orange...

Hope it makes sense.

Good point n_1, how about instead of making 'Service Name' the sole alternate / lookup key, I include 'Effective Date' in the alternate / lookup key, meaning that if by chance a service does change processes, it will be 'end-dated', and a new row will be created with the same service name, but new effective date ( and new primary key )... Meaning that from a DB point of view, the old service parameters will still exist, but will not be linked to a current service... Then I can just make the application side clean those up... At least that way I dont have to kill the actual service-name and start a new one ( but I will have two rows for it, one history and one current ), and I wont have a 'valid' service who has changed processes and have parameters rows pointing to it which are no longer correct... Does that sound right ?

Looks good. The only concern would be if a service "mutates" twice during the same day - in that case how would you know which of the two services with the same name and effective date is the most current one?

Even if it's an Oracle-like DATE type, which is more like a timestamp, I would probably choose a separate attribute to indicate the most current service instance.