The ORM Foundation

Yes in this domain you can have all three, or any combination of nulls or country codes.  The idea in the domain I'm working off of assumes that its important to have as much data as possible filled out as well as a default value in case no country code was found.  So to sumarize, a claimant can be "Investigated by" Federal ...

I think I'm way over my head on this one.  But I can make some basic logical declarations about this particular domain. A Claimant can only have one country code in this domain used to determine living expenses. A Federal Agency object, an organization object, and a Claimant object can either have a country code with a ...

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.

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 ...

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.502, null, us, af, 55.003, null, fr, us, 45.504, us, us, af, ...

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 ...

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 ...

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 ...

 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 ...

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 ...