The ORM Foundation

Hi, I am running into a problem modeling a table that covers where a county code exists or not in 5 different situations where a penalty is applied based on what the first in the chain country is not null and is mapped correctly.  I want to remodel the database but keep the business logic clearly defined in one or more tables and am looking for the best in practice method.

 I want to follow a chain of whether the field is null or not null for 5 different cases, and remodel the database.  Here is an example.

Highest priority: Federal Agency's Country code

Next Highest: State Agency's Country code

next : County organization's Country code

next: City's Country code

next: Claimants Country code

based on which one is found first in the chain a penalty is assigned and a maintenance fee is also attributed for each country where by a mapping of every country and every penalty and maintenance fee is already predetermined and agreed upon by all parties using the system.  At any point in the chain if a null is found it drops to the next lower priority in the chain.  Is there a good way to keep this type of chain logic clearly defined when redesigning a database and how would I go about doing that in orm?  thx

Hi Marc,

Sounds like an interesting modeling problem. I think it would make it clearer, however, if you could give one or two concrete examples (e.g. table rows) of data used in these fields... and maybe even offer an interpretation of these data use cases. If you can't share any of the real data, please invent one or two such examples. This would help me a lot, I think, in understanding this business logic you're trying to model... Thanks much,

Regards,

Andy

Your description is not very clear. I can think of a couple of different things you might mean though, so I'll try to answer based on that. If I'm mistaken, please clarify and we'll have another go.

One possibility is that you have a record which has a single country code, but the source or provenance or authority which provided that fact is one of the five you listed. In that case, I'd simply record two attributes, one for the country code and one for the authority. That seems too simple, so it probably isn't what you mean.

A second possibility is that a record may have any one or more of the five codes, and you want to use the most authoritative. In this situation, model five separate attributes (many->one fact types), all optional (or perhaps make it mandatory that one is provided - use a disjunctive mandatory constraint). Now it remains to derive the most authoritative. In ORM, you need to use a textual notation for the derivation. I would treat the lowest-priority pair, and derive the most authoritative one (if at least one is populated). Then do the same for the third, the fourth, and the fifth. That tells you which code to use, and/or a similar derivation can derive the penalty. There's various ways to do this, but this seems the most obvious. I can think of some alternatives using objectified fact types and subtyping, but it's much less obvious what's going on.

Finally, you might have the situation represented by the attached ORM2 diagram (drawn using my APRIMO ORM tool). In this situation you have a separate table containing country-code assignments for the Place, at most one for each Authority (and reliance on that authority incurs the given Penalty). It's more highly normalised than option 2, but will not perform as well (though it does have more flexibility to ass a new Authority). Again, you must derive the Penalty (being the least penalty of those associated with country code assignments for the given Place), and can also derive the most trusted or authoritative country code to use.

I hope that helps. I haven't given the derivation syntax, because it varies between tools, and the free version of NORMA doesn't support any of them

 I forgot to add the country to the Cost of Living table.  But this is basically what I'm trying to do find the cost of living based the country code.  The Federal Agency takes, presidence, then comes the Public Organization(charity) then the Claimant's Country code is last.  I'm using this as a watered down example to see what the best practice would be:

The country rate is set no matter whether any of the three are valid.  I'm currently using a join that looks for the existence of the Federal Agency's country code, then the next case is it look for the non-existence of the federal agency's Country code and the existence only the Public organization. The final case in this example is it look for the non existence of both the Federal Agency and the public organization's country code and for the existence of the Claimant's country code.  If it finds no country code, it then defaults to the AF's rate.

US - $57.00/day

CN = $49.00/day

AF = $40.00/day

It seems to me that it might be easier for you to define some atomic facts and then expand as required. The following model illustrates this approach.
Also, it's not good practice to use ".id" as a reference mode for every object. My model shows some alternatives.

Ken

Note: Click on the diagram to see the right hand side.

Hi Marc,

 Does the below seem to reflect accurately the fact types and business logic in view in the domain (of your "watered-down example")?

I gather that the actual, existing database has a "CostOfLiving" table. This seems like something that needs correcting, rather than reflecting accurately the fact types of interest in the domain. But let me know if the above is incorrect, and in what way. Then we can move forward based on an agreed understanding of the basic business logic in view.

Thanks,

Andy

Yes that seems to reflect accurately the way I currently want to design the database, thanks.   The current model can have all three cases b/c the more info the better as well as nulls, I only put the exclusive or b/c I wanted to relay the importance of the existence of the first case as the most important, then the next importance if it exists, then finally the last case. 

I'm not sure how you added those light blue [homeCountry] headers but that seems useful.

I would prefer to keep the constraint logic (derivation) in the joins or in a where clause.  I'm assuming the logic for the constraints or conditions can't be added to sql from orm currently.  But instead would have to be created by some manual process.  Or after the orm tool created the baseline sql or schema.

The less than or equal to 1 frequency constraint would be very useful in this instance for default cases.

The only problem is adding in all the special cases where this logic would be trumped.

Thanks,

Marc

Thanks.  I'm trying to use various identifiers to clearly define my schema.

 As far as this intermediate table it is an agreement that is pre-determined, but the order of presidence dictates that the existence of certain pieces takes presidence over other pieces regardless of wether they exist or not.  So I'm tryingt to build a complicated derivation rule that takes all these cases as well as special cases into account.  I'm not 100% sure I need to do this, or if I should create special table with all this logic built in.  The main goal is to prevent developers from creating business rules in disparate code outside the sql server environment.

Hi Marc,

I have responded in-line, like

 >> this

mnnoon:

Yes that seems to reflect accurately the way I currently want to design the database, thanks.  

>> Good, now we can go ahead and address finer details (e.g. see below).

...

I'm not sure how you added those light blue [homeCountry] headers but that seems useful.

>> These headers are role-names. In NORMA, you can select the rolebox, then go to its Properties sheet and fill in the Name property, and this header will appear on the diagram.

I would prefer to keep the constraint logic (derivation) in the joins or in a where clause.  I'm assuming the logic for the constraints or conditions can't be added to sql from orm currently.  But instead would have to be created by some manual process.  Or after the orm tool created the baseline sql or schema.

>> OK, now that we have a "pure" conceptual model, undistracted by implementation details, let's have a look at how to enforce this derivation rule in SQL (which, as I take it, is what you're currently trying to do). Could you, in simple, horizontal notation, lay out for me / us the structures of the relational tables in which the above fact types have been implemented? (E.g., Employee (empId, gender, birthdate, homeCountry, ...).) And, please give an indication of whether you're trying to implement this rule in a (set of) trigger(s), or rather in a check clause, or what... And, do you need to derive-and-store? or is it just derive-on-query (and don't store)? This will give us a framework in which to consider the problem.

The less than or equal to 1 frequency constraint would be very useful in this instance for default cases.

>> I put that unary in, btw, because I wasn't 100% sure what 'AF' is. One would think it's a country code - for Afghanistan, in fact - in this context... but is that really the case?? (Why Afghanistan, one might well ask?) But even if that is Afghanistan, we could have this unary be either an asserted or a derived f.t. (derived only if one can be sure that Afghanistan's ICOL is recorded in the database...) In either case, that role-cardinality constraint on the unary should actually be '#=1', rather than '#<=1'.

The only problem is adding in all the special cases where this logic would be trumped.

>> Yes, but that shouldn't be very hard, now that we know the basic pattern... I'll give you the first shot at it, though, as you're more familiar with the domain and in particular, with these special cases. Cheers, Andy

Thanks,

Marc

Hi Andy et al, 

I think this is what you wanted? 

CREATE SCHEMA "Exists"
GO

CREATE TABLE "Exists".Claimant
(
 claimantId INTEGER IDENTITY (1, 1) NOT NULL,
 hasUnlistedWorkHistory BIT,
 federalAgencyId INTEGER IDENTITY (1, 1),
 publicOrganizationId INTEGER,
 homeCountry NATIONAL CHARACTER(2),
 CONSTRAINT Claimant_PK PRIMARY KEY(claimantId)
)
GO

CREATE TABLE "Exists".PublicOrganization
(
 publicOrganizationId INTEGER IDENTITY (1, 1) NOT NULL,
 countryCode NATIONAL CHARACTER(2) NOT NULL,
 CONSTRAINT PublicOrganization_PK PRIMARY KEY(publicOrganizationId)
)
GO

CREATE TABLE "Exists".Country
(
 countryCode NATIONAL CHARACTER(2) NOT NULL,
 cost DECIMAL(7,2),
 CONSTRAINT Country_PK PRIMARY KEY(countryCode)
)
GO

ALTER TABLE "Exists".Claimant ADD CONSTRAINT Claimant_FK1 FOREIGN KEY (publicOrganizationId) REFERENCES "Exists".PublicOrganization (publicOrganizationId) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE "Exists".Claimant ADD CONSTRAINT Claimant_FK2 FOREIGN KEY (homeCountry) REFERENCES "Exists".Country (countryCode) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE "Exists".PublicOrganization ADD CONSTRAINT PublicOrganization_FK FOREIGN KEY (countryCode) REFERENCES "Exists".Country (countryCode) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

Insert into "Exists".Country ('us', 55.00)

Insert into "Exists".country ('fr', 45.00)

Insert into "Exists".Country ('af', 42.00)

Hi Marc,

This is, if I'm not mistaken, a DDL script based on the ORM diagram I posted. I was looking, rather, for some indication of what is the schema that you're trying to write your constraint for, in SQL.

But maybe I should make sure of this question first: Are you planning to re-do a current relational schema (to make it more accurate or efficient) that handles these kinds of facts? Or do you just want to understand the logic of that rule better, in order to write it correctly (in a trigger or other code) in SQL?

I was sort of assuming the latter, and was therefore asking about the structure of the schema for which you had to write the SQL. But maybe I assumed wrong; so please answer that question about your intentions. That will steer me in the right direction.

Cheers,

Andy

Thanks for the analysis. 

What I'd like is for these constraints to be built into the model as constraints without having to build a matrix of each claimant

[claimantid], [fedagency country code], [org country code], [claimant country code], [cost of living]

1, null, af, us, 41.50

2, null, us, af, 55.00

3, null, fr, us, 45.50

4, us, us, af, 55.00

5, af, us, us, 41.50

6, null, null, fr, 45.50

As you can see this is based on whether one of three combination exist at 3 different levels.  I was wandering if I can enforce this constraint at the model level.

 Thank you,

Marc

What you've described is the second option I presented in my original response. In SQL, you can do this with a CASE statement, to derive the effective country code, then a join from this to the Country table to get the cost of living.

In ORM, you must use a derived fact type - but none of the current tools support automatic mapping to SQL (except NORMA Pro, which isn't properly "available" yet).

In SQL, a query like this works (though depending on your SQL engine you may be able to do it more succinctly, say using ISNULL):

SELECT *,
     CASE
      WHEN federalAgencyId IS NOT NULL
      THEN (SELECT countryID FROM FederalAgency AS f WHERE f.id = federalAgencyId)
      WHEN publicOrganizationId IS NOT NULL
      THEN  (SELECT countryID FROM PublicOrganization AS p WHERE p.id = publicOrganizationId)
      ELSE homeCountry
      END AS EffectiveCountry
FROM Claimant

From that, you can join EffectiveCountry to the Country table to get the cost of living.

Thanks Clifford,

 I'm using a join to do the same thing not sure which is better.  But in the join I do need to start at the top, and then as I go down to the lower priority organizations I would check to ensure that the spots for the higher priority organizations above are indeed null.

 Id like to try out that Norma vPro when its available.

I'd prefer to use an else statement after the test for null and in my join I also test for empty string '' which I don't think that represents a null.  This seems to be the case where too many table constraints have been relaxed.