From: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Approaches for Lookup values (codes) in OLTP application |
Date: | 2008-02-15 17:09:06 |
Message-ID: | 50062.216.185.71.22.1203095346.squirrel@webmail.harte-lyne.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)Harte-Lyne(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-02-15 17:38:01 | Re: Approaches for Lookup values (codes) in OLTP application |
Previous Message | Dave Page | 2008-02-15 17:07:15 | Re: Pains in upgrading to 8.3 |