Any ORM (Hibernate) boffins about?

[suityou01]

I am currently designing a data warehouse and have had the most bizarre conversation with the lead developer who says it is not possible to write an ORM class for one of my tables. (Don't ask why he is writing an ORM class for a data warehouse )

So the table accepts delta loads from a source system

PK Source_ID Int
PK Load_Date DateTime
Field_1
Field_2

So the primary key of the table is the source id from the source system, and the datetimestamp.

Easy enough.

How is it hard to write an ORM class to represent one row?

Take the following example

Source_ID Load_Date Field_1 Field_2 Field_3
123 14/03/2010 09:00 ABC DEF GHI
123 14/03/2010 09:15 ABC DEF JKL

So he needs to write one ORM class for Source_ID with a collection of ORM classes (as a property of the first class) to represent each row for that source_id ordered in descending date order.

He could even add a flag to the constructor to indicate a kind of "lazy load" of only the latest row.

Anyone spot a problem with this?

[eek]

I think the problem he has is that he does not have a unique identifier to attach his Get request to. Give him an identity field or a sequence and send him on his way.

Then ignore the field when you do the real work.

[suityou01]

I think the problem he has is that he does not have a unique identifier to attach his Get request to. Give him an identity field or a sequence and send him on his way.

Then ignore the field when you do the real work.

And therein lies the problem, I don't understand what is wrong with using the PK as the unique identifier.

Linky

[doodab]

And therein lies the problem, I don't understand what is wrong with using the PK as the unique identifier.

Linky

Nothing is wrong with using the PK as the unique identifier for a row, but you seem to want a class that represents multiple rows.

[thunderlizard]

So he needs to write one ORM class for Source_ID with a collection of ORM classes (as a property of the first class) to represent each row for that source_id ordered in descending date order.

I disagree at that point. With this data-first approach, the ORM class should represent the same as what 1 row in the database table does. Otherwise you're really going against the grain. The unique identifier for the class should be a <composite-id> of source ID and timestamp, like it is in the database.

If you want to group them by source ID, you're introducing business logic, not representing the database. Do that in code elsewhere.

[suityou01]

Nothing is wrong with using the PK as the unique identifier for a row, but you seem to want a class that represents multiple rows.

Yeah sort of.

class somerows {

private someRow m_colRows();

somerows (flag loadLatestOnly, int source_Id) //If latest only then only load the latest row
{

//Build list of keys (source_id and load_date as composite key relating to the source_id argument) and instantiate an instance of someRow and stuff into the collection.
}

}

Then add a property to the class someRows to retrieve the someRow instances.

[suityou01]

I disagree at that point. With this data-first approach, the ORM class should represent the same as what 1 row in the database table does. Otherwise you're really going against the grain. The unique identifier for the class should be a <composite-id> of source ID and timestamp, like it is in the database.

If you want to group them by source ID, you're introducing business logic, not representing the database. Do that in code elsewhere.

Did you follow the link I posted about composite keys? That is exactly what I want.

[eek]

And therein lies the problem, I don't understand what is wrong with using the PK as the unique identifier.

Linky

Because wearing my awkward sod hat who is to say the following won't occur

Source_ID Load_Date Field_1 Field_2 Field_3
123 14/03/2010 09:00 ABC DEF GHI
123 14/03/2010 09:15 ABC DEF JKL
123 14/03/2010 09:15 ABC DEF MNO

and you no longer have a primary key to play about with.

[suityou01]

Because wearing my awkward sod hat who is to say the following won't occur

Source_ID Load_Date Field_1 Field_2 Field_3
123 14/03/2010 09:00 ABC DEF GHI
123 14/03/2010 09:15 ABC DEF JKL
123 14/03/2010 09:15 ABC DEF MNO

and you no longer have a primary key to play about with.

The data is trigger driven so there can be no clashes.

The story is, user in source system updates or creates one record - trigger fires, insert happens.
The datetimestamp here could go to seconds or milliseconds if it pleases you

[doodab]

Did you follow the link I posted about composite keys? That is exactly what I want.

class somerows {

private someRow m_colRows();

somerows (flag loadLatestOnly, int source_Id) //If latest only then only load the latest row
{

//Build list of keys (source_id and load_date as composite key relating to the source_id argument) and instantiate an instance of someRow and stuff into the collection.
}

}

Then add a property to the class someRows to retrieve the someRow instances.

A class with a composite key will still give you one object per row. What you are calling someRows will be a collection or array of those objects.

What you could do is either have an instance object per row class and a Facade class to encapsulate the business logic of "get the latest row for this particular source id" or a source object and a row object with proper one to many relationship between "sources" and the rows in the table.