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@xxxxxxxxxxxxx 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
