On Sat, 2007-04-21 at 18:31 -0700, Arthaey Angosii wrote:
> I want to allow "notes" on any row in any table in my database. A
> table row may have multiple notes. Say my tables are "foo," "bar," and
> "qux." I want to avoid having a lookup table for each of them
> ("foo_notes," "bar_notes," and "qux_notes").
>
> Is there a standard way of solving this problem?
>
> Not knowing SQL all that well, I thought that maybe I could have a
> "notes" table: [...]
> But I have no idea how I could use notes.table_name and notes.row_id
> to relate (table_name.id = row_id) to notes.note.
You can save some space by referring to the table by its OID:
CREATE TABLE notes (
id SERIAL,
table_id OID,
row_id BIGINT,
note VARCHAR NOT NULL,
PRIMARY KEY(id) );
CREATE INDEX notes_table_row_idx ON notes (table_id,row_id);
You could even write one query to get some data and the notes from all
tables:
SELECT 'Bar',tableoid,oid,NULL,x,y FROM bar
UNION ALL
SELECT 'Foo',tableoid,oid,name,NULL,NULL FROM foo;
However, if you want to do what you're asking about, you're probably
working in some object-oriented framework. You might have queries that
look like:
SELECT x,y FROM bar WHERE id=?
This would change to
SELECT x,y FROM bar WHERE id=? -- returns 1 row, if id is valid
SELECT note FROM bar
LEFT OUTER JOIN notes
ON bar.tableoid=notes.table_id AND bar.id=notes.row_id
WHERE id=? -- returns 0..* rows
It might be easier to create a base-class that includes your preferred
ID generation logic, the "note" column (possibly just a nullable id for
the seperate "notes" table), any any other application-wide columns
(responsible user, modification date...) that you want to use. You
could even use Postgres inheritance for that base table, although you
wouldn't have to.
One last question: do you want notes on the "notes" table itself, if
you create one?
