Обсуждение: table referencing several others

Hello,

To support a forum application I have a "forum" table:

   Column    |            Type
-------------+-----------------------------
 created_by  | integer
 created_on  | timestamp without time zone
 modified_by | integer
 modified_on | timestamp without time zone
 id_forum    | integer
 id_parent   | integer
 subject     | text
 message     | text
 id_show     | integer
 id_thread   | integer
 id_person   | integer


The id_show and id_person columns are meant to point to a show or person
object about which the specific forum thread is started (threads can
also have a "free" subject and these fields are then empty).

Now I'd like to add forums about locations (theatres, museums, etc.) so
I am thinking about adding an id_location column, but this method seems
a bit inelegant to me:

- every subsequent message of the thread will carry these useless fields
  (only the first message in thread uses one of them),

- if I want to connect a forum to a new type of object, yet a new
  id_<object> will be required,

Is there a better way to have a single field that can relate with
several objects and still benefit from integrity checks?

Thanks,

    Here what I would do :

    - Have a table "objects", which contains locations, shows, persons, well
anything you want to relate to the forums in question.
    - Simply add an object_id to your forums table.

    Now, how do you distinguish between different types of objects in your
objects table ?
    You can add an object_type field and teach your object-relational mapper
that it expresses the class of object that should be instantiated when
fetching the rows.
    You can have fields that are only used by some types of objects, and null
for others.
    You can use table inheritance.
    You can also have an objects table, and tables locations_extra_infos,
people_extra_info which store the fields that are unique to each type of
object.

> - every subsequent message of the thread will carry these useless fields
>   (only the first message in thread uses one of them),

    If the fields are set to NULL, they will only use one bit, so this is not
a problem.
    Do not store the location_id in each message of the thread : if you want
to change it, you'll have to update all the messages !