I am considering how best to handle the issue of attribute encoding for an
OLTP application conversion. The existing system, which does not employ a
relational DBMS in the commonly accepted sense, uses a "system_table" to
validate system codes. This dataset uses concatenated fields to form a unique
key. The fields are "table_name", "table_column_name", and "value_as_char".
The conversion project framework is Ruby on Rails which embeds the practice of
arbitrary integer primary keys assigned by sequencers rather than so-called
"natural" keys or predicates that define the unique portion of the table-row.
My questions revolve around how best to implement this in postgresql given the
expectations of Rails. Is it best that I create a table with the three key
columns and an additional id then have a unique index on the three values but
store the id in the referential row? Do I store the code value in the
referential row and use the implied table_name, table_column_name and stored
value to preform a lookup on the system_table? Is there another approach that
I am not aware of that is superior to both of these?
Comments most welcome.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
