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

-----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-----