Allow non-unique records in usp_lrel_asset_chgnr table

[Zsolt TWlow6FseWk=]

Hello!

 

I would like to add one affected CI more times to a Change ticket. With SoapUI I can add a CI once, but cant do it again.

I didnt find any constraints on the table.

 

Any ideas, how can i solve this?

Thank you!

[Lindsay_Estabrooks]

Perhaps you could explain the business case behind needing a CI associated with the same Change Order more than once?

[Zsolt TWlow6FseWk=]

hi,

 

first of all, thanks for the answers.

 

 

The business case: We mark the service outage_start and outage_end timestamp of the affected CI-s in the usp_lrel_asset_chgnr table with additional columns. If we add a CI to a change we have to mark the outage start and stop aswell.

However, there are CI-s which have more outage periods during the change and we want to mark them separately to get the accurate outage time. Thats the reason why we need to get rid of the constraint.

 

Thanks!

 

MichaelMueller

[Lindsay_Estabrooks]

So you have changes that occur over a (relatively) lengthy time that take CIs offline multiple times and you want the CIs to be available for users between those times. One idea I propose is that you break the change down into multiple Change Orders (one for each time that the CI is taken offline). Then tie the Change Orders together with a parent Change Order.

[James Campbell]

Another possibility might be to add a classic workflow task to the change for each CI outage.

[Michael Mueller]

Okay, understood.

 

You might need to think about your data schema.
In your case, you dont have a dual relationship between change and ci, which holds an outage info.

 

You have a ternary relationship between change , ci, and outage.

 

You should have a separate outage object with a relation to the usp_lrel_asset_chgnr relation

 

That way you would be able to handle a list of outages for a single change affected ci.

 

At least from a schema perspective, this would be a more reliable approach, than to attach the same ci more than once to a change.
Additionally you wouldn't harm the basic schema layout of SDM , and may be influence other app parts  ,  which rely on this standard  constraint.

 

Regards
.....Michael

[Marc Horlomus]

hi,

 

even there is no index on my machine on db level, too, there is a 

INDEX SORT ASCENDING UNIQUE ( ORDER_BY ) chg nr ;

constraint in ddict.sch.

 

greetings, pacy

[Michael Mueller]

maybe take another closer look at the DB again.

I do have such a unique index in my test env. And if it is in the ddict.sch, it should be in the DB as well, If not, something is wrong!

 

However, I fully agree to Lindsay_Estabrooks, and ask myself, what exactly is the business use case here?

Regards

…..Michael

[Marc Horlomus]

hi,

 

of course you are right

my fault, i looked to fast in the wrong column.....i also have this index, it's non-clustered but unique

 

sry Kennedyzso and thx mikelm!