Relationships between multiple tables 2

[khokanson]

I have three tables that contain information for an invoicing system. One table, InvoiceImage, contains image information with multiple records for each invoice and the other two tables, InvoicePending and Invoices, are for pending and approved invoices.

As it occurs now I have to create the initial invoice record in InvoicePending then associate image records in InvoiceImage to said invoice using columns named InvoiceID (reference to the PK in the invoice table) and TableID (signifies which invoice table to use).

When the invoice is approved I have to trensfer the pending invoice from InvoicePending into Invoices and update the InvoiceID & TableID columns in the InvoiceImage to point to the new location.

What I need to know is if there is a way to define a relationship between the InvoiceID columns of InvoiceImage & InvoicePending and then InvoiceImage & Invoices seperatley based on the value in InvoiceImage.TableID.

I understand that under normal circumstances there should only be one invoice table with a status column which would solve this problem very easily BUT certain political issues coupled with design concerns preclude that from being a possibility.

 

[rac2]

Arent the relationshiops always the same, only the data changes?

The relationship for pending invoices is expressed in a query JOINing InvoiceImages and InvoicesPending. The other JOIN is InvoiceImages and Invoices. The nvoiceImages.InvoiceID value will be matched with the InvoicesPending.InvoiceID value in the one and with Invoices.InvoiceID in the other. So what if the IDs have different meanings?

Hmmm. Maybe it would be a good idea to limit the rows using the TableID column on the chance that the values might line up. I think I get it now.

The pending relationship-

SELECT im.*, p.*
FROM InvoicesPending p
JOIN InvoiceImages im ON im.InvoiceID = p.InvoiceID
                                    AND im.TableID = 'Pending'

The approved relationship-

SELECT im.*, a.*
FROM Invoices a
JOIN InvoiceImages im ON im.InvoiceID = a.InvoiceID
                                    AND im.TableID = 'Approved'

What I try to do when a manager proceeds to design my database and my code is nod in agreement, murmur "Yes, I see. Yes, that is a good idea." Then go ahead and do it the right way. They never check, cause if they were actually interested in these things they would be programmers, not managers.

 

[SQLSister]

Creating a relationship onteh tables is a problem as the final table will not have the id field in it until it is moved there, so then the child table won;t allow it tio enter the record.

Can you make it so that the invoice table uses the same id field as the InvoicePending table? Rather than making it the primary key field, make it a unique index instead and then you can insert the values from the other table. The put a trigger on the invoice table to update the tableId field in the image table on insert.

If you can;t do that, could you start storing the INvoicePending ID inthe INvoice table in addition to the invoice ID? THen you still use a trigger to update both fields by joining onthe invoicependingId field to the image table.

Questions about posting. See faq183-874

 

[rac2]

Question to SQLSister.

When you say "Creating a relationship" do you mean to create a foreign key constraint on the table?

Would it be possible then to use
ALTER TABLE Invoices CONSTRAINT constraint_name DISABLE
to permit the insert?

 

[SQLSister]

Yes I mean a foreign key constraint. You could disable it or make it so that it doesn't check the value, but then why have a constraint? Disabling just for the insert is dangerous because of the simultaneous user problem. Two users are doing an insert in nearly simultaneously - user one disables the constraint, inserts the record at which point the second user disables the constraint to get ready for the insert. Just then the first user re-enables the constraint which causes the second user's insert to fail. Better to handle through triggers in my opinion.

Questions about posting. See faq183-874

 

[khokanson]

Thank you both for your ideas. I think I am going to have to lean towards adding an InvoicePendingID column to my images table.

Here's the situation. I work for a construction company and I write enhancements to our current accounting and job cost tracking software. I do so with the good graces of the company which orriginally wrote the software. Unfortunately this software company creates pretty bad table structures which are often difficult to work with.

My current project which allows my company to scan in all of our invoices we receive and associate the images with the invoice records in our accounting system works really well so far. I am simply trying to add some check constraints which will not allow our accounting system to delete invoice records thus stranding the image records.

Changing the existing data stucture in any way is a recipe for disaster but what I can do is add my work in seperate tables as a supplement. I can even tweak existing tables in the form of added constraints/indexes etc. With that in mind I think ya'll have me pointing in the best (read not right but close) direction.

Again, thank you for your ideas on the matter.