Relational Database Question 1

[DMBLuva]

Hi all,
I've been going back and forth on the relationships I have for a particular database and am now desparate enough to bother anyone who will read/assist.
I've got a database that needs to track financial transactions. Normally this would be cut and dry for me, but I think I've been looking at it too long that I just am not seeing it clear anymore.
My office has expenditures and invoices (submitted by companies) they must track to a project. The kicker is, the expenditure is the total value that can be spent and if it goes over they need another...an invoice may or may not fall under these expenditures. So this means that if an invoice is submitted for this project, it might just be a floating invoice that needs to be tracked to the project. We are ultimately reviewing the total of money spent on the project in the end.
So the long and short of it is the expenditure doesn't necessarily link to the invoice but it links the case. And the expenditure links to the project no matter what, but might not ever have an invoice. Therefore I have put a link table for when they do. On the flip side, I have put a link table to join the company and the project number and that link ID is related to the invoice and expenditure. Oh and I have to be able to show how much is remaining in the expenditure and be able to add an existing invoice to another (new) expenditure...ahhh. But that's another crazy problem.
Here is my structure I'm abbreviating names, this is not my naming convention):

ProjectLinktable:
ID (linked 1 to many, Expenditure and Invoice)
CompanyID
ProjectID

ExpenditureInvoiceLinkTable:
ID
ExpenditureID (linked 1 to many ExpenditureTable)
InvoiceID (linked 1 to many InvoiceTable)

CompanyTable:
ID (linked 1 to many CompanyEmployeeTable)
CompanyName

ProjectTable:
ID (Linked 1 to many to the projectlinktable)
etc

ExpenditureTable:
ID (linked 1 to many ExpenditureInvoicetable)
LinkTableID (linked 1 to many, ProjectLinkTable)
Etc

InvoiceTable:
ID (linked 1 to many ExpenditureInvoicetable)
LinkTableID (linked 1 to many, ProjectLinkTable)
etc

Hopefully this is making sense, I have to admit I get into a tunnel with the relationship rules that I have a hard time seeing clearly. Any help is much appreciated.

Sarah

 

[MajP]

I am not sure if this is your issue, because I am a little lost. However, the following works and does not violate normalacy. You can set up a foreign key that relates to different tables. Again not sure if this is the issue, but assume an invoice is normally related to an expenditure but occaisonally it is directly related to a project and not a specific expenditure.

tblInvoiceLink
invoiceLink_ID
expenditureID_fk
projectID_fk

So an invoice has a link to tblInvoiceLink. Each record either has a expeditureID_fk or a ProjectID_FK, which is a foreign key to one of the two tables. Now everything still links back to a project either through the projectID_fk or through the exependiture which links to a project.

Now the issue here is that you cannot enforce referential integrity because only one of the foreign keys is required.

This design is common with a memo field "notes" table. You can have a single notes table that provides notes for numerous items in different tables.

 

[DMBLuva]

MajP,
You are right there in my head! See I changed from that set up as I thought it was a waste of data. To be clear there is no need for an auto ID in that table right?
For example:

tblinvoiceLink
InvoicelinkID ExpenditureID_fk ProjectID_fk
1 (Null) 6
(null) 1 6
1 1 6

Later an invoice can be linked to an expenditure.

I thought this was the wrong way to go since you didn't always have a link to all three of them. What would I do with the Company? Would that also go into the link table since the invoice AND the expenditure has to have the same company assigned to them or since the expenditure and invoice can be independant of eachother that doesn't make sense?

Sorry if I wasn't clear in my first post. I should have explained a little better. I just wanted to make sure my thinking was on track since I've been having some issues with join queries. The way I have it currently seems to be working so far, it's just that I need to be able to calculate the remaining balance of the expenditure and I didn't know if that set up would cause me more trouble than it worth. But more than likely it's my VBA.

Thank you so much for taking a look at this.

 

[MajP]

Not certain but, I was thinking differently. If I understand

An expenditure always links to a project and only one project

tblExpenditure
expenditureID
expenditure fields
foreign key to project

Now that I rethought about this, I think you can simplify it. An invoice can relate directly to an expenditure or directly to a project but has to be one. I think I might simply do it this way

tblinvoice
invoice fields
ExpenditureID_fk
ProjectID_fk

Here is the trick and you can do this at the form level. If you assign directly to an expenditure leave the the project. If you associate it directly to a project then fill in the ProjectID_fk. No real need to have both filled in. At the form level ensure that one filled in (thus ensuring referential integrity).

Since a an expenditure relates to a project and some invoice relate to expenditures.
You can make a query that returns for a project all expenditure related invoices by linking the project table to the expenditure table and linking on the expenditureID in the invoice table.

You can make a query that returns for a project only the project level invoices by joining the project table directly to the invoice table on theProjectID_fk. Add "where expenditureID_FK is null." to ensure that you do not double count the invoices related to expenditures.

A union query of the two would give all invoices (expenditure level and project level) for the project

Summing up any values does not require vba. You have all the invoices for a project so simply use sql to give any totals.

 

[DMBLuva]

You are a genius! It helps so much to have it looked at by someone who hasn't analyzed it for what feels like a million hours.

I'm going to give it a shot. I'll research my sql (kind of a newbie). Thank you again!