SQL Question

seidel

I have this table

link_table

document_number|link_doc_1||link_doc_2||link_doc_3||link_doc_4||link_doc_5||link_doc_6|
AM0101 | AM0101 | CS0101 | AM0103 | AM0111 | AM0151 | AM0191 |
AM0102 | CS0101 | AM0101 | PP0101 | RR0101 | 0 | 0 |
AM0103 | 0 | 0 | 0 | 0 | 0 | 0 |
AM0104 | AM0101 | TT0101 | AM0131 | AM0111 | AR0103 | PP0101 |
AM0105 | GG0131 | AM0101 | AM0121 | DD0901 | AM0908 | AM9801 |
AM0106 | RR0101 | FF0191 | 0 | 0 | 0 | 0 |
*AM0107 | AP1010 | AM0101 | 0 | 0 | 0 | 0 |

I want my SQL statement to return all document_number fields that have AM0101 in one of
the Link_doc_$ fields

SELECT document_number
FROM link_table
WHERE ? = AM0101

The result for this example table would be:

AM0101
AM0102
AM0104
AM0105
*AM0107

So basiscally the SQL would search through all the link_doc_$ fileds and return the document
number is criteria was met

I know this is a simple solution, but how?

Thanks PHP community for your help

Anon

Why not concatenate all the link fields together to make one field and then search within that field for 'AM0101'.

seidel

my database will scale to a large size is this efficient? if so how would I do something like this?

reagent

That doesn't look like the most normalized table design. I'm not exactly sure what you are trying to accomplish, but normalizing your data will help your queries in the long run.

If you tell me what all this document number storage is for, i may be able to help w/ your normalization. You're right, BTW.. there has to be a better way..

seidel

OK your right, this table design sucks!!!

However in the begining of my project I was not sure how to loop properly with PHP (cold fusion background) so I built the table like you see it

This is the run down

********History************
I am creating a documetn management system for DHL(parcell delivery co.) what they want is a system where users can create documents(via special interface)and then send them on to supivisors for approval.

Part of the document creation process is that users can link other documents to there document that they are creating(this has to do with some internal Hiearchy of document management ,some documents are dependent of others)

I get the link documents from another table The final_main_table, I assume that a document will not exceed 20 links(therefore I created a table final_link_doc that would accept up to 20 link_documents)

So when the user chooses what documents to link they are inserted into the final_link_doc table

final_link_doc TABLE

document_number [primary key]
link_doc_1
link_doc_2
link_doc_3
link_doc_4
link_doc_5
link_doc_6
link_doc_7
link_doc_8
link_doc_9
link_doc_10
link_doc_11
link_doc_12
link_doc_13
link_doc_14
link_doc_15
link_doc_16
link_doc_17
link_doc_18
link_doc_19
link_doc_20

final_main TABLE
document_number [priamary key]
document_title
purpose
scale
date_created
written_by
approved_by

Well everything works fine so far, but now I need to build a system that will e-mail the user who created the document if there is a revision.

************example***********

user Chris creates docuemnt AM0101
he links document AM0101 to document PP0101.(exsisting document in final_main TABLE)

user Jennifer makes edits to document PP0101
after these edits the system needs to notify all authors who created documents that are linked to PP0101 that there has been a change made to PP0101(this is to insure that if a process changes in one document that it does not change the other document process, this referes to the Hiearchy I mentioned before)

so what I need to do is select all the document_number fields in TABLE final_link_doc that have PP0101 in one of the link_doc_% fields.

Then I will generate a mail() by author names in final_main

so user Chris will get a e-mail that document PP0101 has been modified please check that this modification does not afect your document AM0101

I hope this makes sense.

If yo have a better Idea on how to stor these link documents please let me know

thanks

christopher

reagent

Ok,
2 scenarios:
can a user just create a document?
if i have 4 documents 1 - 4 (for simplicity sake)

if document 2 is linked to doc 1 and 3 is linked to 2 and 4 is also linked to 2, should i pull up all documents when i view document 1?

ie. (-> represents a link)
1 -> 2
2 -> 3
2 -> 4

is this valid?

i'm thinking of a table design for you. Your first clue that your design 'sucks' as you say, was that you 'assumed' there would be no more than 20 links, and that you started appending 1, 2, etc to your field names 😉

hth
p.

seidel

a user can create a document and can also revise a document.
If I understand you correctly

links
1 -> 2
2 -> 3
2 -> 4

when I view document 1,
I should only see a link to document 2

when I view document 2,
I sould only see a link to documents 3 and 4

if I revise document 4,
an e-mail should go to the author of document 2

etc....

thanks

seidel

a user can create a document and can also revise a document.

*************example *************
If I understand you correctly

links
1 -> 2
2 -> 3
2 -> 4

when I view document 1,
I should only see a link to document 2

when I view document 2,
I sould only see a link to documents 3 and 4

if I revise document 4,
an e-mail should go to the author of document 2

etc....

thanks

reagent

Ok, here's a schema i came up with to replace your link table (you can keep the main table)

CREATE TABLE link_table (
parent_id_tx VARCHAR(32) NOT NULL,
child_id_tx VARCHAR(32) NOT NULL,
PRIMARY KEY (parent_id_tx, child_id_tx)
);

should give you a table where you have the scenario:

parent -> child
AM0101 -> CS0202
AM0101 -> SP0334
AB4353 -> AM0101

so if document 'AM0101' changes, you can notify everyone else.

select
parent_id_tx,
child_id_tx
from
link_table
where
parent_id_tx = 'AM0101'
or
child_id_tx = 'AM0101'

this will get you all ids for documents that are related to AM0101. you may want to modify it if you want to get just children or parents affected..

of course, it will also give you back a bunch of 'AM0101' results as you are selecting back all ids in the table. if you split it into two looking for parents and children you can minimize this..

hth.
p