On Tue, 11 Jan 2005 21:06:21 -0500, Jeremy Cowgar <jeremy@...> wrote:
> I have about 9 tables that all accept notes. So, I created one note table
> that looks something like:
> CREATE TABLE notes (
> id int unsigned auto_increment primary key,
> obj_type int,
> obj_id int unsigned,
> title varchar(80),
> body text);
> In each object I have a .OBJ_TYPE field that has a 1, 2, 3, etc... Then, when
> a note is added, I use both the .OBJ_TYPE and the object id to insert into
> the notes table. This gives me 1 table, and also when searching for
> information this is a great plus.
> I think you can see the problem already. How can I relate to that table in my
> 9 different objects? There needs to be an additional condition on my link. Is
> this possible? If not, is my database design bad?
> Jeremy Cowgar
When I need to do this I use a slightly different solution. In each
table I'm going to make notes available I add another column like
NOTE_ID INTEGER. Then I create a notes table which looks something
CREATE TABLE application_notes
id INTEGER AUTO_INCREMENT primary key
Whenever a new note is added, check the source table note_id, if it
is populated then use it when creating the application_notes record,
if not allocate the next number in sequence. Then, when retrieving
notes for a record you use the NOTE_ID as the foreign key and just
select all of the records from application_notes with the appropriate
For instance, with a couple of customer records like;
ID: 1, Name: Andy Todd, Address: Somewhere in Australia, Note_Id : 1
ID: 2, Name: Jeremy Cowgar, Address: Somewhere else, Note_Id: 2
The appropriate application_notes records would look like;
ID: 1, Note_Id: 1, Note_sequence: 1, title: 'Andy is Great', body: ...
ID: 2, Note_Id: 1, Note_sequence: 2, title: 'Andy is still fabulous', body: ...
ID: 3, Note_Id: 2, Note_sequence: 1, title: 'Jeremy needs notes', body: ...
ID: 4, Note_Id: 1, Note_sequence: 3, title: 'Andy doesnt need any more
notes', body: ...
From the desk of Andrew J Todd esq