Thread: Indirect access to data, given table name as a string

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:

CREATE TABLE notes (
    id integer primary key,
    table_name regclass not null,
    row_id integer not null,
    note text not null
);

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. I've looked a little
bit at information_schema and the system catalog, but I haven't found
any examples of what I'm trying to do, so I don't know if I'm on the
right track here.

Any suggestions would be appreciated!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/21/07 20:31, 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:
>
> CREATE TABLE notes (
>     id integer primary key,
>     table_name regclass not null,
>     row_id integer not null,
>     note text not null
> );
>
> 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. I've looked a little
> bit at information_schema and the system catalog, but I haven't found
> any examples of what I'm trying to do, so I don't know if I'm on the
> right track here.
>
> Any suggestions would be appreciated!

For this kind of scheme, add a "note_id integer" to each relevant
"main" table, and then have your notes table look like this:

CREATE TABLE notes (
    id integer,
    row_id smallint not null,
    note text not null,
    primary key (id, row_id)
);

You'll also need an "id_master" table to keep track of the next id.
 (A serial datatype would not work because the PK is compound.)

This makes sense to me.  I hope I was able to elucidate it clearly.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGLMmAS9HxQb37XmcRAgMLAKCgc/bEq8GlqZPeGooeRopxLcilQACfalUb
mUV+4cZ3lv6Bas5KGuoKLUE=
=MEQY
-----END PGP SIGNATURE-----

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?