Hello.

I have a really tricky sql problem and would appreciate any help I can
get.

My database stores clients, jobs, contacts and notes. Each note can
potentially (but not necessarily) be related to a client, a job and a
contact. This relationship is maintained by a by a small relationship
table which stores the note id, the note's owner's id and the type of
owner.

At the moment, if I'm dealing with a particular client, I can retrieve
all their notes using something like the following:

SELECT notes.id, notes.content, notes.date
FROM notes, clients, notes_relations

WHERE notes.id = notes_relations.note_id
AND notes_relation.fid = clients.id
AND notes_relations.fid_type = 5 /* some predefined code for clients
*/

Here's the issue: a note returned from the above could potentially be
related to a job or a contact and I need to know if that's the case.
Because I'm using PHP, I can extract the note id from the result and
find a related job using:

SELECT jobs.id
FROM notes, jobs, notes_relations

WHERE notes.id = __EXTRACTED_NOTE_ID__
AND notes.id = notes_relations.note_id
AND notes_relations.fid = jobs.id
AND notes_relations.fid_type = 10 /* some predefined code for jobs */

This is definitely achieving the correct result; unfortunately it's
just taking way too long. The first transaction takes under a second;
subsequently determining related jobs and contacts adds at least 10
seconds to processing time.

Also, I'm sure there's a way using JOIN to extract the required info in
a single query.

Can anyone help me?

EMAIL REMOVED wrote:

> Hello.
>
> I have a really tricky sql problem and would appreciate any help I can
> get.
>
> My database stores clients, jobs, contacts and notes. Each note can
> potentially (but not necessarily) be related to a client, a job and a
> contact. This relationship is maintained by a by a small relationship
> table which stores the note id, the note's owner's id and the type of
> owner.
>
> At the moment, if I'm dealing with a particular client, I can retrieve
> all their notes using something like the following:
>
> SELECT notes.id, notes.content, notes.date
> FROM notes, clients, notes_relations
>
> WHERE notes.id = notes_relations.note_id
> AND notes_relation.fid = clients.id
> AND notes_relations.fid_type = 5 /* some predefined code for clients
> */
>
> Here's the issue: a note returned from the above could potentially be
> related to a job or a contact and I need to know if that's the case.
> Because I'm using PHP, I can extract the note id from the result and
> find a related job using:
>
> SELECT jobs.id
> FROM notes, jobs, notes_relations
>
> WHERE notes.id = __EXTRACTED_NOTE_ID__
> AND notes.id = notes_relations.note_id
> AND notes_relations.fid = jobs.id
> AND notes_relations.fid_type = 10 /* some predefined code for jobs */
>
> This is definitely achieving the correct result; unfortunately it's
> just taking way too long. The first transaction takes under a second;
> subsequently determining related jobs and contacts adds at least 10
> seconds to processing time.
>
> Also, I'm sure there's a way using JOIN to extract the required info in
> a single query.
>
> Can anyone help me?

I haven't studied your post in detail but it sounds like a structural
problem. As I understand it, a note can refer to one or more of the
following - client,job,contact - and MUST refer to at least one of
them. If a note can refer to, say, a client in the context of more than
one contact or job (but not ALL jobs or contacts ***ociated with that
client) then I suspect you could represent that with a db that looks
like this (untested):

clients(client_id*,client)
jobs(job_id*,job)
contacts(contact_id*,contact)
notes(note_id*,note)
note_relationships(client_id*,job_id*,contact_id*, note_id*)

Note how the PK of the note_relationships table comprises the PKs of
ALL the other tables - this allows for 2 or more notes to be applied to
the same job, client,contact, or (I think) for 1 or more of those
values to appear as NULL in the relationships table - like this:

client_id | job_id | contact_id | note_id
1 NULL NULL 1
1 1 NULL 2
2 1 NULL 2
1 NULL NULL 3

Anyway, if I'm wrong about using NULL values in a PK, then you just
need to add another id column to this table, and make that the PK
instead.

Now it's easy to select all notes based upon client, contact, job or
any combination of those. This might not be the most readily scalable
solution - but it doesn't sound like that's going to be too much of an
issue.

strawberry wrote:
> EMAIL REMOVED wrote:
>
> > Hello.
> >
> > I have a really tricky sql problem and would appreciate any help I can
> > get.
> >
> > My database stores clients, jobs, contacts and notes. Each note can
> > potentially (but not necessarily) be related to a client, a job and a
> > contact. This relationship is maintained by a by a small relationship
> > table which stores the note id, the note's owner's id and the type of
> > owner.
> >
> > At the moment, if I'm dealing with a particular client, I can retrieve
> > all their notes using something like the following:
> >
> > SELECT notes.id, notes.content, notes.date
> > FROM notes, clients, notes_relations
> >
> > WHERE notes.id = notes_relations.note_id
> > AND notes_relation.fid = clients.id
> > AND notes_relations.fid_type = 5 /* some predefined code for clients
> > */
> >
> > Here's the issue: a note returned from the above could potentially be
> > related to a job or a contact and I need to know if that's the case.
> > Because I'm using PHP, I can extract the note id from the result and
> > find a related job using:
> >
> > SELECT jobs.id
> > FROM notes, jobs, notes_relations
> >
> > WHERE notes.id = __EXTRACTED_NOTE_ID__
> > AND notes.id = notes_relations.note_id
> > AND notes_relations.fid = jobs.id
> > AND notes_relations.fid_type = 10 /* some predefined code for jobs */
> >
> > This is definitely achieving the correct result; unfortunately it's
> > just taking way too long. The first transaction takes under a second;
> > subsequently determining related jobs and contacts adds at least 10
> > seconds to processing time.
> >
> > Also, I'm sure there's a way using JOIN to extract the required info in
> > a single query.
> >
> > Can anyone help me?
>
> I haven't studied your post in detail but it sounds like a structural
> problem. As I understand it, a note can refer to one or more of the
> following - client,job,contact - and MUST refer to at least one of
> them. If a note can refer to, say, a client in the context of more than
> one contact or job (but not ALL jobs or contacts ***ociated with that
> client) then I suspect you could represent that with a db that looks
> like this (untested):
>
> clients(client_id*,client)
> jobs(job_id*,job)
> contacts(contact_id*,contact)
> notes(note_id*,note)
> note_relationships(client_id*,job_id*,contact_id*, note_id*)
>
> Note how the PK of the note_relationships table comprises the PKs of
> ALL the other tables - this allows for 2 or more notes to be applied to
> the same job, client,contact, or (I think) for 1 or more of those
> values to appear as NULL in the relationships table - like this:
>
> client_id | job_id | contact_id | note_id
> 1 NULL NULL 1
> 1 1 NULL 2
> 2 1 NULL 2
> 1 NULL NULL 3
>
> Anyway, if I'm wrong about using NULL values in a PK, then you just
> need to add another id column to this table, and make that the PK
> instead.
>
> Now it's easy to select all notes based upon client, contact, job or
> any combination of those. This might not be the most readily scalable
> solution - but it doesn't sound like that's going to be too much of an
> issue.

Thanks for your reply!

This definitely a potential solution. My only concern is this: the
scenario I outlined in the original post is a simplified version of the
real schema in that notes can be related to many other entity types;
e.g.: interviews, consultants, references, etc. which would mean that
the notes_relations table would need as many fields as there are
relationship types. E.g.:

client_id | job_id | contact_id | interview_id | consultant_id |
reference_id | .... | note_id

Is this good database design? Is there a better solution?

Thanks again.

EMAIL REMOVED wrote:
> strawberry wrote:
>> EMAIL REMOVED wrote:
>>
>>> Hello.
>>>
>>> I have a really tricky sql problem and would appreciate any help I can
>>> get.
>>>
>>> My database stores clients, jobs, contacts and notes. Each note can
>>> potentially (but not necessarily) be related to a client, a job and a
>>> contact. This relationship is maintained by a by a small relationship
>>> table which stores the note id, the note's owner's id and the type of
>>> owner.
>>>
>>> At the moment, if I'm dealing with a particular client, I can retrieve
>>> all their notes using something like the following:
>>>
>>> SELECT notes.id, notes.content, notes.date
>>> FROM notes, clients, notes_relations
>>>
>>> WHERE notes.id = notes_relations.note_id
>>> AND notes_relation.fid = clients.id
>>> AND notes_relations.fid_type = 5 /* some predefined code for clients
>>> */
>>>
>>> Here's the issue: a note returned from the above could potentially be
>>> related to a job or a contact and I need to know if that's the case.
>>> Because I'm using PHP, I can extract the note id from the result and
>>> find a related job using:
>>>
>>> SELECT jobs.id
>>> FROM notes, jobs, notes_relations
>>>
>>> WHERE notes.id = __EXTRACTED_NOTE_ID__
>>> AND notes.id = notes_relations.note_id
>>> AND notes_relations.fid = jobs.id
>>> AND notes_relations.fid_type = 10 /* some predefined code for jobs */
>>>
>>> This is definitely achieving the correct result; unfortunately it's
>>> just taking way too long. The first transaction takes under a second;
>>> subsequently determining related jobs and contacts adds at least 10
>>> seconds to processing time.
>>>
>>> Also, I'm sure there's a way using JOIN to extract the required info in
>>> a single query.
>>>
>>> Can anyone help me?
>> I haven't studied your post in detail but it sounds like a structural
>> problem. As I understand it, a note can refer to one or more of the
>> following - client,job,contact - and MUST refer to at least one of
>> them. If a note can refer to, say, a client in the context of more than
>> one contact or job (but not ALL jobs or contacts ***ociated with that
>> client) then I suspect you could represent that with a db that looks
>> like this (untested):
>>
>> clients(client_id*,client)
>> jobs(job_id*,job)
>> contacts(contact_id*,contact)
>> notes(note_id*,note)
>> note_relationships(client_id*,job_id*,contact_id*, note_id*)
>>
>> Note how the PK of the note_relationships table comprises the PKs of
>> ALL the other tables - this allows for 2 or more notes to be applied to
>> the same job, client,contact, or (I think) for 1 or more of those
>> values to appear as NULL in the relationships table - like this:
>>
>> client_id | job_id | contact_id | note_id
>> 1 NULL NULL 1
>> 1 1 NULL 2
>> 2 1 NULL 2
>> 1 NULL NULL 3
>>
>> Anyway, if I'm wrong about using NULL values in a PK, then you just
>> need to add another id column to this table, and make that the PK
>> instead.
>>
>> Now it's easy to select all notes based upon client, contact, job or
>> any combination of those. This might not be the most readily scalable
>> solution - but it doesn't sound like that's going to be too much of an
>> issue.
>
> Thanks for your reply!
>
> This definitely a potential solution. My only concern is this: the
> scenario I outlined in the original post is a simplified version of the
> real schema in that notes can be related to many other entity types;
> e.g.: interviews, consultants, references, etc. which would mean that
> the notes_relations table would need as many fields as there are
> relationship types. E.g.:
>
> client_id | job_id | contact_id | interview_id | consultant_id |
> reference_id | .... | note_id
>
> Is this good database design? Is there a better solution?
>
> Thanks again.
>

IMHO, this is a very poor design.

Rather, maybe 3 columns: NoteId, ReferenceId, ReferenceType

A NoteID will refer to a specific ReferenceType (i.e. client_id, job_id,
etc.) with the id of ReferenceId.

Anytime you have multiple columns with basically the same information,
especially when many of the columns are NULL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

On Jan 24, 11:37 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> battle.ch...@gmail.com wrote:
> > strawberry wrote:
> >> battle.ch...@gmail.com wrote:
>
> >>> Hello.
>
> >>> I have a really tricky sql problem and would appreciate any help I can
> >>> get.
>
> >>> My database stores clients, jobs, contacts and notes. Each note can
> >>> potentially (but not necessarily) be related to a client, a job and a
> >>> contact. This relationship is maintained by a by a small relationship
> >>> table which stores the note id, the note's owner's id and the type of
> >>> owner.
>
> >>> At the moment, if I'm dealing with a particular client, I can retrieve
> >>> all their notes using something like the following:
>
> >>> SELECT notes.id, notes.content, notes.date
> >>> FROM notes, clients, notes_relations
>
> >>> WHERE notes.id = notes_relations.note_id
> >>> AND notes_relation.fid = clients.id
> >>> AND notes_relations.fid_type = 5 /* some predefined code for clients
> >>> */
>
> >>> Here's the issue: a note returned from the above could potentially be
> >>> related to a job or a contact and I need to know if that's the case.
> >>> Because I'm using PHP, I can extract the note id from the result and
> >>> find a related job using:
>
> >>> SELECT jobs.id
> >>> FROM notes, jobs, notes_relations
>
> >>> WHERE notes.id = __EXTRACTED_NOTE_ID__
> >>> AND notes.id = notes_relations.note_id
> >>> AND notes_relations.fid = jobs.id
> >>> AND notes_relations.fid_type = 10 /* some predefined code for jobs */
>
> >>> This is definitely achieving the correct result; unfortunately it's
> >>> just taking way too long. The first transaction takes under a second;
> >>> subsequently determining related jobs and contacts adds at least 10
> >>> seconds to processing time.
>
> >>> Also, I'm sure there's a way using JOIN to extract the required info in
> >>> a single query.
>
> >>> Can anyone help me?
> >> I haven't studied your post in detail but it sounds like a structural
> >> problem. As I understand it, a note can refer to one or more of the
> >> following - client,job,contact - and MUST refer to at least one of
> >> them. If a note can refer to, say, a client in the context of more than
> >> one contact or job (but not ALL jobs or contacts ***ociated with that
> >> client) then I suspect you could represent that with a db that looks
> >> like this (untested):
>
> >> clients(client_id*,client)
> >> jobs(job_id*,job)
> >> contacts(contact_id*,contact)
> >> notes(note_id*,note)
> >> note_relationships(client_id*,job_id*,contact_id*, note_id*)
>
> >> Note how the PK of the note_relationships table comprises the PKs of
> >> ALL the other tables - this allows for 2 or more notes to be applied to
> >> the same job, client,contact, or (I think) for 1 or more of those
> >> values to appear as NULL in the relationships table - like this:
>
> >> client_id | job_id | contact_id | note_id
> >> 1 NULL NULL 1
> >> 1 1 NULL 2
> >> 2 1 NULL 2
> >> 1 NULL NULL 3
>
> >> Anyway, if I'm wrong about using NULL values in a PK, then you just
> >> need to add another id column to this table, and make that the PK
> >> instead.
>
> >> Now it's easy to select all notes based upon client, contact, job or
> >> any combination of those. This might not be the most readily scalable
> >> solution - but it doesn't sound like that's going to be too much of an
> >> issue.
>
> > Thanks for your reply!
>
> > This definitely a potential solution. My only concern is this: the
> > scenario I outlined in the original post is a simplified version of the
> > real schema in that notes can be related to many other entity types;
> > e.g.: interviews, consultants, references, etc. which would mean that
> > the notes_relations table would need as many fields as there are
> > relationship types. E.g.:
>
> > client_id | job_id | contact_id | interview_id | consultant_id |
> > reference_id | .... | note_id
>
> > Is this good database design? Is there a better solution?
>
> > Thanks again.IMHO, this is a very poor design.
>
> Rather, maybe 3 columns: NoteId, ReferenceId, ReferenceType
>
> A NoteID will refer to a specific ReferenceType (i.e. client_id, job_id,
> etc.) with the id of ReferenceId.
>
> Anytime you have multiple columns with basically the same information,
> especially when many of the columns are NULL.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

I replied to this post about 12hrs ago so I'm going to ***ume the post
isn't going to show up; sorry if it does.

Thanks for your reply Jerry.

The schema you've suggested seems exactly the same as the one we
started out with with different column names; i.e.: NoteId, ReferenceId
and ReferenceType correlate with note_id, fid and fid_type.

I think this is the best representation, but it still leaves the
original problem: I need to be able to retrieve all notes that relate
to a particular entity of some type (e.g. a Client) and, using the same
query, retrieve details of other entities related to the retrieved
notes.

E.g.
- The interface renders the notes for CompanyX.
- One of those notes states 'Job987 was completed today. Invoice
sent.'
- Job987 is also related to the note via a notes_relations record, as
is 'Terry Hatcher', the consultant who undertook the work.
- Clicking on the Job987 or 'Terry Hatcher' links will refresh the
browser with that entities' details.

So the query needs to not only retrieve the notes, but other related
entities as required.

Thanks again for your help.

On 24 Jan, 23:53, battle.ch...@gmail.com wrote:
> On Jan 24, 11:37 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>
>
>
>
> > battle.ch...@gmail.com wrote:
> > > strawberry wrote:
> > >> battle.ch...@gmail.com wrote:
>
> > >>> Hello.
>
> > >>> I have a really tricky sql problem and would appreciate any help I can
> > >>> get.
>
> > >>> My database stores clients, jobs, contacts and notes. Each note can
> > >>> potentially (but not necessarily) be related to a client, a job and a
> > >>> contact. This relationship is maintained by a by a small relationship
> > >>> table which stores the note id, the note's owner's id and the type of
> > >>> owner.
>
> > >>> At the moment, if I'm dealing with a particular client, I can retrieve
> > >>> all their notes using something like the following:
>
> > >>> SELECT notes.id, notes.content, notes.date
> > >>> FROM notes, clients, notes_relations
>
> > >>> WHERE notes.id = notes_relations.note_id
> > >>> AND notes_relation.fid = clients.id
> > >>> AND notes_relations.fid_type = 5 /* some predefined code for clients
> > >>> */
>
> > >>> Here's the issue: a note returned from the above could potentially be
> > >>> related to a job or a contact and I need to know if that's the case.
> > >>> Because I'm using PHP, I can extract the note id from the result and
> > >>> find a related job using:
>
> > >>> SELECT jobs.id
> > >>> FROM notes, jobs, notes_relations
>
> > >>> WHERE notes.id = __EXTRACTED_NOTE_ID__
> > >>> AND notes.id = notes_relations.note_id
> > >>> AND notes_relations.fid = jobs.id
> > >>> AND notes_relations.fid_type = 10 /* some predefined code for jobs */
>
> > >>> This is definitely achieving the correct result; unfortunately it's
> > >>> just taking way too long. The first transaction takes under a second;
> > >>> subsequently determining related jobs and contacts adds at least 10
> > >>> seconds to processing time.
>
> > >>> Also, I'm sure there's a way using JOIN to extract the required info in
> > >>> a single query.
>
> > >>> Can anyone help me?
> > >> I haven't studied your post in detail but it sounds like a structural
> > >> problem. As I understand it, a note can refer to one or more of the
> > >> following - client,job,contact - and MUST refer to at least one of
> > >> them. If a note can refer to, say, a client in the context of more than
> > >> one contact or job (but not ALL jobs or contacts ***ociated with that
> > >> client) then I suspect you could represent that with a db that looks
> > >> like this (untested):
>
> > >> clients(client_id*,client)
> > >> jobs(job_id*,job)
> > >> contacts(contact_id*,contact)
> > >> notes(note_id*,note)
> > >> note_relationships(client_id*,job_id*,contact_id*, note_id*)
>
> > >> Note how the PK of the note_relationships table comprises the PKs of
> > >> ALL the other tables - this allows for 2 or more notes to be applied to
> > >> the same job, client,contact, or (I think) for 1 or more of those
> > >> values to appear as NULL in the relationships table - like this:
>
> > >> client_id | job_id | contact_id | note_id
> > >> 1 NULL NULL 1
> > >> 1 1 NULL 2
> > >> 2 1 NULL 2
> > >> 1 NULL NULL 3
>
> > >> Anyway, if I'm wrong about using NULL values in a PK, then you just
> > >> need to add another id column to this table, and make that the PK
> > >> instead.
>
> > >> Now it's easy to select all notes based upon client, contact, job or
> > >> any combination of those. This might not be the most readily scalable
> > >> solution - but it doesn't sound like that's going to be too much of an
> > >> issue.
>
> > > Thanks for your reply!
>
> > > This definitely a potential solution. My only concern is this: the
> > > scenario I outlined in the original post is a simplified version of the
> > > real schema in that notes can be related to many other entity types;
> > > e.g.: interviews, consultants, references, etc. which would mean that
> > > the notes_relations table would need as many fields as there are
> > > relationship types. E.g.:
>
> > > client_id | job_id | contact_id | interview_id | consultant_id |
> > > reference_id | .... | note_id
>
> > > Is this good database design? Is there a better solution?
>
> > > Thanks again.IMHO, this is a very poor design.
>
> > Rather, maybe 3 columns: NoteId, ReferenceId, ReferenceType
>
> > A NoteID will refer to a specific ReferenceType (i.e. client_id, job_id,
> > etc.) with the id of ReferenceId.
>
> > Anytime you have multiple columns with basically the same information,
> > especially when many of the columns are NULL.
>
> > --
> > ==================
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > ==================I replied to this post about 12hrs ago so I'm going to ***ume the post
> isn't going to show up; sorry if it does.
>
> Thanks for your reply Jerry.
>
> The schema you've suggested seems exactly the same as the one we
> started out with with different column names; i.e.: NoteId, ReferenceId
> and ReferenceType correlate with note_id, fid and fid_type.
>
> I think this is the best representation, but it still leaves the
> original problem: I need to be able to retrieve all notes that relate
> to a particular entity of some type (e.g. a Client) and, using the same
> query, retrieve details of other entities related to the retrieved
> notes.
>
> E.g.
> - The interface renders the notes for CompanyX.
> - One of those notes states 'Job987 was completed today. Invoice
> sent.'
> - Job987 is also related to the note via a notes_relations record, as
> is 'Terry Hatcher', the consultant who undertook the work.
> - Clicking on the Job987 or 'Terry Hatcher' links will refresh the
> browser with that entities' details.
>
> So the query needs to not only retrieve the notes, but other related
> entities as required.
>
> Thanks again for your help.- Hide quoted text -- Show quoted text -
I have just done a similar thing with user and company tables.

Could you post the schema (as CREATE TABLE statements), some relevant
sample data (as INSERT statements)
and some required results?

EMAIL REMOVED wrote:
>
> I replied to this post about 12hrs ago so I'm going to ***ume the post
> isn't going to show up; sorry if it does.
>
> Thanks for your reply Jerry.
>
> The schema you've suggested seems exactly the same as the one we
> started out with with different column names; i.e.: NoteId, ReferenceId
> and ReferenceType correlate with note_id, fid and fid_type.
>
> I think this is the best representation, but it still leaves the
> original problem: I need to be able to retrieve all notes that relate
> to a particular entity of some type (e.g. a Client) and, using the same
> query, retrieve details of other entities related to the retrieved
> notes.
>
> E.g.
> - The interface renders the notes for CompanyX.
> - One of those notes states 'Job987 was completed today. Invoice
> sent.'
> - Job987 is also related to the note via a notes_relations record, as
> is 'Terry Hatcher', the consultant who undertook the work.
> - Clicking on the Job987 or 'Terry Hatcher' links will refresh the
> browser with that entities' details.
>
> So the query needs to not only retrieve the notes, but other related
> entities as required.
>
> Thanks again for your help.
>

Why does it have to be the same query? In fact, your description
indicates it probably should be 2 queries - the first one returning the
notes, then after clicking, a second query to retrieve the ***ociated data.

You wouldn't want to retrieve all information for all notes. That could
be a lot of unnecessary information if there are say 100 notes and the
user only wants info on one of them.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================