The ORM Foundation

If I understand correctly, it would be best to create a fact that ties the entity (person, trust, corp), account and address together. Rather than trying to tie the account to an address.

I think I need to see a model that allows the accounts to know which of the person addresses it is attached to. Is there somewhere online that offers examples?

Jake,
I have created an example for you. However, my attempts to upload the example to the "feature requests" section of this forum have been met with the response "error" and no explanation.
Any suggestions?
Ken

Hi there, James Hardiman here; ORM aficionado for the last 14 or so years.

I missed the original post here, so I can't comment directly, but I'm fascinated by the direction in which this discussion is going.  As I come at ORM from a somewhat different direction from Ken, I thought I'd throw in my 10c worth.

For me, one of the key things that ORM gives us is the ability to model the UoD from the users' point of view, without having to worry about the technical implementation until much later.  Hence Terry calling this "conceptual modeling".

It is usually the case IMHO (and experience), that domain users and experts have great experience of *working* in their domain, but don't have great experience of thinking very carefully about it.

The sort of discussion that has been happening here is VITAL to have between the application designer and the user community.  However, I believe that it is not the responsibility of the designer to impose any particular view on the community, but to act as a sort of latter-day Socrates, and enter into a dialogue with them (probably using good old-fashioned Socratic dialectic) to explore their thinking about the domain in order to discover (and this will be a process of discovery for both sides) what the *best* way will be to represent that domain.

So, I don't think that you will resolve this discussion until we introduce someone from a credit card company who understands the UoD from the users' point of view, and until we have entered into a Socratic dialogue with him or her to thoroughly examine the UoD.

During such a process, we can represent in ORM whatever the current thinking about the domain may happen to be.

When we arrive at a consensus that our model is "correct" (in the users' terms), and we can't see anything that we could dialectically challenge, then we can click the appropriate button in whatever tool we are using to see if the model is mathematically correct.

At that point we generate the ER diagram, and then it's over to the DBA.  Whatever is done after that, whether it be de-normalisation for reasons of performance, or whatever, is down to the DBA's black arts ... and that's where I have to leave the conversation!

Regards,

Socrates2006

I agree with Ken so far regarding this discussion. I also feel that what James has said has an important part as well.

Back to the original problem and the suggested solution, the desired results do not require the tool to handle something that could be modeled differently. The reference mode of id could be removed from address, an external uniqueness constraint could be added to the necessary part of an address, and the one to one could be changed to a many to one and the desired results would occur. 

Off Topic: I have put in a feature request to provide a different forum solution, due to the lack of features (i.e. images). Please feel free to provide input, and let the powers that be decide the best solution.

Hi Jake,
I have had no luck in uploading my very pretty file complete with ORM Schema, Logical Schema and Fact report. The best I can do is to show you the DDL. Here it is for SQL Server.
Hope this helps.
Ken

  /*    This SQL DDL script was generated by Microsoft Visual Studio. */

/*    Driver Used : Microsoft Visual Studio - Microsoft SQL Server Driver.                    */
/*    Document    : C:\Documents and Settings\Ken Evans\My Documents\ORM2_NORMA\SourceForgeExample1_LS.vsd. */
/*    Time Created: 11 March 2006 18:37.                                                      */
/*    Operation   : From Visio Generate Wizard.                                               */
/*    Connected data source : No connection.                                                  */
/*    Connected server      : No connection.                                                  */
/*    Connected database    : Not applicable.                                                 */



/* Create CreditCard database.                                                                */
use master  

go

create database "CreditCard"  

go

use "CreditCard"  

go

/* Create new table "Account billed Person Address".                                          */
/* "Account billed Person Address" : Account is billed to Person at Address                   */
/* 	"Account ID" : Account contains a record of transactions,                                 */
/* 	"Person id" : Address is a legal postal address.                                          */
/* 	"Address id" : Address is a legal postal address.                                         */  
create table "Account billed Person Address" ( 
	"Account ID" char(10) not null,
	"Person id" char(10) not null,
	"Address id" char(10) not null)  

go

alter table "Account billed Person Address"
	add constraint "Account billed Person Address_PK" primary key ("Account ID", "Address id")   


go

/* Create new table "CreditCard billed Person Address".                                       */
/* "CreditCard billed Person Address" : CreditCard is billed to Person at Address             */
/* 	"CreditCard" : CreditCard provides the authorised user(Person) with Services.             */
/* 	"Person id" : Address is a legal postal address.                                          */
/* 	"Address id" : Address is a legal postal address.                                         */  
create table "CreditCard billed Person Address" ( 
	"CreditCard" char(10) not null,
	"Person id" char(10) not null,
	"Address id" char(10) not null)  

go

alter table "CreditCard billed Person Address"
	add constraint "CreditCard billed Person Address_PK" primary key ("CreditCard", "Address id")   


go

/* Create new table "Person Account Bank".                                                    */
/* "Person Account Bank" : Person has Account with Bank                                       */
/* 	"Person id" : Address is a legal postal address.                                          */
/* 	"Account ID" : Account contains a record of transactions,                                 */
/* 	"Bank ID" : Role three (Bank) of fact: Person has Account with                            */  
create table "Person Account Bank" ( 
	"Person id" char(10) not null,
	"Account ID" char(10) not null,
	"Bank ID" char(10) not null)  

go

alter table "Person Account Bank"
	add constraint "Person Account Bank_PK" primary key ("Person id", "Bank ID")   


go

/* Create new table "Person".                                                                 */
/* "Person" : Address is a legal postal address.                                              */
/* 	"Person id" : Address is a legal postal address.                                          */
/* 	"Lives Address id" : Address is a legal postal address.                                   */
/* 	"Name" : Person has Name                                                                  */  
create table "Person" ( 
	"Person id" char(10) not null,
	"Lives Address id" char(10) not null,
	"Name" char(10) not null)  

go

alter table "Person"
	add constraint "Person_PK" primary key ("Person id")   


go

/* Create new table "CreditCard".                                                             */
/* "CreditCard" : CreditCard provides the authorised user(Person) with Services.              */
/* 	"CreditCard" : CreditCard provides the authorised user(Person) with Services.             */
/* 	"Person id" : Address is a legal postal address.                                          */
/* 	"Account ID" : Account contains a record of transactions,                                 */
/* 	"Issued Bank ID" : CreditCard is issued by Bank                                           */  
create table "CreditCard" ( 
	"CreditCard" char(10) not null,
	"Person id" char(10) not null,
	"Account ID" char(10) not null,
	"Issued Bank ID" char(10) not null)  

go

alter table "CreditCard"
	add constraint "CreditCard_PK" primary key ("CreditCard")   


go

/* Create new table "Bank".                                                                   */
/* "Bank" : Table of Bank                                                                     */
/* 	"Bank ID" : Bank is identified by BankID                                                  */
/* 	"Address id" : Address is a legal postal address.                                         */  
create table "Bank" ( 
	"Bank ID" char(10) not null,
	"Address id" char(10) not null)  

go

alter table "Bank"
	add constraint "Bank_PK" primary key ("Bank ID")   


go

/* Create new table "Address".                                                                */
/* "Address" : Address is a legal postal address.                                             */
/* 	"Address id" : Address is a legal postal address.                                         */
/* 	"City" : Address has City                                                                 */
/* 	"ZipCode" : Address has ZipCode                                                           */  
create table "Address" ( 
	"Address id" char(10) not null,
	"City" char(10) not null,
	"ZipCode" char(10) not null)  

go

alter table "Address"
	add constraint "Address_PK" primary key ("Address id")   


go

/* Add the remaining keys, constraints and indexes for the table "Account billed Person Address". */
alter table "Account billed Person Address" add constraint "Account billed Person Address_UC1" unique (
	"Account ID",
	"Person id")  


go

/* Add the remaining keys, constraints and indexes for the table "CreditCard billed Person Address". */
alter table "CreditCard billed Person Address" add constraint "CreditCard billed Person Address_UC1" unique (
	"CreditCard",
	"Person id")  


go

/* Add the remaining keys, constraints and indexes for the table "Person Account Bank".       */
alter table "Person Account Bank" add constraint "Person Account Bank_UC1" unique (
	"Person id",
	"Account ID")  


go

alter table "Person Account Bank" add constraint "Person Account Bank_UC2" unique (
	"Account ID",
	"Bank ID")  


go

/* Add foreign key constraints to table "Account billed Person Address".                      */
alter table "Account billed Person Address"
	add constraint "Person_Account billed Person Address_FK1" foreign key (
		"Person id")
	 references "Person" (
		"Person id")  

go

alter table "Account billed Person Address"
	add constraint "Address_Account billed Person Address_FK1" foreign key (
		"Address id")
	 references "Address" (
		"Address id")  

go

/* Add foreign key constraints to table "CreditCard billed Person Address".                   */
alter table "CreditCard billed Person Address"
	add constraint "CreditCard_CreditCard billed Person Address_FK1" foreign key (
		"CreditCard")
	 references "CreditCard" (
		"CreditCard")  

go

alter table "CreditCard billed Person Address"
	add constraint "Person_CreditCard billed Person Address_FK1" foreign key (
		"Person id")
	 references "Person" (
		"Person id")  

go

alter table "CreditCard billed Person Address"
	add constraint "Address_CreditCard billed Person Address_FK1" foreign key (
		"Address id")
	 references "Address" (
		"Address id")  

go

/* Add foreign key constraints to table "Person Account Bank".                                */
alter table "Person Account Bank"
	add constraint "Person_Person Account Bank_FK1" foreign key (
		"Person id")
	 references "Person" (
		"Person id")  

go

alter table "Person Account Bank"
	add constraint "Bank_Person Account Bank_FK1" foreign key (
		"Bank ID")
	 references "Bank" (
		"Bank ID")  

go

/* Add foreign key constraints to table "Person".                                             */
alter table "Person"
	add constraint "Address_Person_FK1" foreign key (
		"Lives Address id")
	 references "Address" (
		"Address id")  

go

/* Add foreign key constraints to table "CreditCard".                                         */
alter table "CreditCard"
	add constraint "Person_CreditCard_FK1" foreign key (
		"Person id")
	 references "Person" (
		"Person id")  

go

alter table "CreditCard"
	add constraint "Bank_CreditCard_FK1" foreign key (
		"Issued Bank ID")
	 references "Bank" (
		"Bank ID")  

go

/* Add foreign key constraints to table "Bank".                                               */
alter table "Bank"
	add constraint "Address_Bank_FK1" foreign key (
		"Address id")
	 references "Address" (
		"Address id")  

go

/* Create procedure/function Person_Account_Bank_equal1.                                      */
/* /* The constraint:                                                                            */ */
/* /* Implied equality constraint.                                                               */ */
/* /* is enforced by the following DDL.                                                          */ */
Create Procedure Person_Account_Bank_equal1 as
/*   Microsoft Visual Studio generated procedure code. */
if (
    not exists (select * from "Account billed Person Address" X where 
                not exists (select * from "Person Account Bank" Y
                            where X."Account ID" = Y."Account ID")) and
    not exists (select * from "Person Account Bank" X where
                not exists (select * from "Account billed Person Address" Y
                            where Y."Account ID" = X."Account ID"))
)
  return 1
else
  return 2
/* End Person_Account_Bank_equal1                                                             */




go

/* Create procedure/function Account_billed_Person_Address_subset2.                           */
/* /* The constraint:                                                                            */ */
/* /* Implied subset constraint.                                                                 */ */
/* /* is enforced by the following DDL.                                                          */ */
Create Procedure Account_billed_Person_Address_subset2 as
/*   Microsoft Visual Studio generated procedure code. */
if (
    not exists (select * from "CreditCard" X where 
                not exists (select * from "Account billed Person Address" Y
                            where X."Account ID" = Y."Account ID"))
)
  return 1
else
  return 2
/* End Account_billed_Person_Address_subset2                                                  */




go


/* This is the end of the Microsoft Visual Studio generated SQL DDL script.                   */

Jake,

Here is an example of what you're looking for:

Person(1) has Address(1)
Person(1) has Address(2)
Person(1) has Address(3)
Person(1) has CreditCard(1)
Person(1) has CreditCard(2)
CreditCard(1) is charged to Address(1)
CreditCard(2) is charged to Address(3)

Conceptually, these examples indicate the following fact types and constraints:

Person(ID) has Address(ID). 
    It is possible that more than one Person has the same Address.
Person(ID) has CreditCard(ID). 
    Each CreditCard is of some Person. 
    Each CreditCard is of at most one Person.
CreditCard(ID) is charged to Address(ID). 
    Each CreditCard is charged to some Address. 
    Each CreditCard is charged to at most one Address.

You also need an external constraint that guarantees that the Address a CreditCard is charged to is and Address that is of the Person that has the CreditCard.


I hope this helps. Btw, I totally agree with everyone who claims that ORM is for conceptual analysis and modeling only. An ORM model - or part of it - should be turned into a logical data model only. The logical data model, then, is turned into a physical data model. It's the physical data model that should be de-normalized if this is what is needed. Let me also say that the different treatment in SOA of resource data and reference data might very well increase the mass of de-normalized data, but that has nothing to do with ORM.