INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Relational Database Question

Relational Database Question

(OP)
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

RE: Relational Database Question

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.

RE: Relational Database Question

(OP)
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.   

RE: Relational Database Question

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.

RE: Relational Database Question

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close