architecting custom database fields

[timasmith]

So I have a patient database object

create table patient (
patient_id number primary key,
patient_name varchar(40),
birth_dt date,
etc.
)

I have an equivalent java object auto generated from the database table
above.

I also have an auto generated patientList object which collects patient
objects and also implements the abstratTableModel which allows me to
automatically populate the GUI JTable and gain wonderful cheap
functionality.

This worked great but now I want to logically extend the patient table
allowing custom fields at that patient level. This seriously breaks
fouls auto generation of the implemented abstractTableModel - since the
fields cant be determined until runtime from the database.

create table custom_fields (
custom_fields_id number primary key,
related_table_ref_id number,
related_primary_key_id number,
custom_column_id number,
column_database_type number,
int_value number,
double_value number,
date_value date,
string_value varchar2(2000),
long_value long
)

Passing patientLists around was intuitive and one thought was to have a
collection of custom fields owned by each Patient Object - but since
they are generated at runtime I have to look them up, add to *every*
Patient Object and add to the Patient List object. Obviously I do not
want the objects/lists to be aware of the database.

Cant say the whole challenge of custom (logical) fields is an
enterprise pattern I've read about but there must be many common
solutions to architecting the best way - both for the database table
and the objects to represent them.

Any help gladly appreciated.

Tim

 

[Laurent Bossavit]

Tim,

This worked great but now I want to logically extend the patient table
allowing custom fields at that patient level. This seriously breaks
fouls auto generation of the implemented abstractTableModel

"Don't do that, then." (Famous joke.)

Joke aside: *why* are per-patient custom fields necessary ?

Is it truly the case that every single patient will have information
associated with it that is logically of a different nature than what
every other patient has ?

If not (and I expect not) then there must be regularities that allow you
to use "vanilla" fields and entities, rather than "per-patient custom
fields". (What is a DB schema for, or even a class definition, other
than to capture regularities over many objects?)

Laurent

 

[Chris Uppal]

This worked great but now I want to logically extend the patient table
allowing custom fields at that patient level.

Like Laurent, I'm a little skeptical about the need for this (mainly because I
don't see how it adds value for the users). But assuming there is a need, you
could model it as an extensible list of more-or-less arbitrary key-value pairs
owned by each patient. A java.util.Map<String, String> might be sufficient.

-- chris

 

[H. S. Lahman]

Responding to Timasmith...

So I have a patient database object

create table patient (
patient_id number primary key,
patient_name varchar(40),
birth_dt date,
etc.
)

I have an equivalent java object auto generated from the database table
above.

I also have an auto generated patientList object which collects patient
objects and also implements the abstratTableModel which allows me to
automatically populate the GUI JTable and gain wonderful cheap
functionality.

I think this is the root of your problems. You are solving the customer
problem, dealing with the GUI, and accessing persistence with
essentially the same set of objects. To get that to work you need
hard-wiring that becomes broken so badly when the requirements change
that you are faced with a shotgun refactoring.

If you are going to provide something as complex as ad hoc custom
fields, you are going to need a more complex solution based on identity.
To facilitate encoding that I would look to separate the concerns of
the GUI display, persistent storage, and defining custom fields in their
own subsystems. Then you can deal with each view in isolation. IME, it
also helps by formalizing identity mapping between the views in terms of
message-based data-transfer interfaces between the subsystems. Then...

This worked great but now I want to logically extend the patient table
allowing custom fields at that patient level. This seriously breaks
fouls auto generation of the implemented abstractTableModel - since the
fields cant be determined until runtime from the database.

create table custom_fields (
custom_fields_id number primary key,
related_table_ref_id number,
related_primary_key_id number,
custom_column_id number,
column_database_type number,
int_value number,
double_value number,
date_value date,
string_value varchar2(2000),
long_value long
)

Static class definitions just don't work well for ad hoc fields. I
think the best tack to take is the one GUI builders employ. A notion
like Patient with an attribute of Name is broken up in the GUI paradigm
to fit the Window/Control model. So Name becomes its own Control
instance with a relationship to the Window instance for Patient. The
GUI builder then provides all the infrastructure code to display them
all as an apparently seamless unit.

In the GUI paradigm, Window and Control have their own suite of
attributes, many of which have nothing to do with the problem being
solved (e.g., background color). In your case the view of Patient
probably consists of some fixed number of attributes plus a suite of ad
hoc custom attributes. So you have:

1 R1 0..*
[Patient] ----------------------- [AdHocAttribute]
+ ID
+ value
+ typeCode

where typeCode tells the application how to interpret 'value'.

Now your autogeneration works basically the same way except for two
things. One is creation of separate [AdHocAttribute] instances and
instantiating the R1 relationship. You can easily do that with a
"factory" object similar to the GoF construction patterns.

The second difference lies in specifying which [AdHocAttribute] are
defined. You can do that with external configuration data much the same
way as RDB schemas work. That configuration data tells the "factory"
object what to build. Then the user just defines the characteristics of
a new attribute and you store that specification in with the other
external configuration data. IOW, you are essentially modifying an RDB
schema dynamically. Your "factory" object just accesses that "schema"
to do its autoconstruction.

In fact, your RDB schema can map 1:1 to this view by adding the
[AdHocAttribute] table. And your GUI subsystem can map [AdHocAttribute]
into a Control instance easily. The point is that you can create
attributes, display them, and store them all based upon a single generic
specification for what an ad hoc attribute looks like. The only tricky
part is identifying the one-size-fits-all commonality among all ad hoc
attributes and basing the identity specification for all three views on
that. (My example is probably simplistic, but the basic idea is quite
extensible in the same sense that GUI Controls support a wide variety of
detailed differences.)

[BTW, there is a category on Invariants and Parametric Polymorphism in
my blog that may be of interest here.]


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
(e-mail address removed)
Pathfinder Solutions -- Put MDA to Work
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
(888)OOA-PATH

 

[timasmith]

thanks, very helpful and gives me a lot to think about!