Solved

Link 3 tables to 1

Posted on 2011-09-26
18
277 Views
Last Modified: 2012-06-27
I have 3 tables,

vendors,
employees,
billers.

Each of these tables have too many unique fields to combine them into 1 table, but I need to refer to them in my journal table when payments are made to them.

I need to tag them to the journal table. Currently Im using a join table, but it's ugly.

What is the best method for doing this?

Thanks.
0
Comment
Question by:aka_FATCAT
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36616021
Sounds like you are saying the tables do below separately for normalization purposes, so JOIN sounds correct. Maybe post the query, so we can see for sure. Alternative would be to have these all JOIN'd in a view so in CF you only select from the view.
0
 

Author Comment

by:aka_FATCAT
ID: 36627311
To clarify.

So I'm storing their respective data in their respective tables. But when I make a payment to them, I need to tag them in my journal table.

i.e I pay a vendor for goods.

in the journal table I have a column named pay_to_id that points to the pay_to table that in turns points to either the employee, vendor, or biller table. Its ugly.

pay_to - table

pay_to_id, pay_to_type_id, specific_id

The pay_to_type table identifies the payee as employee, vendor, or biller.

Then using that identifier, (pay_to_type_id) I determine which table to look into for the (specific_id).


Can't get any more amateur status than that.


0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36646155
I got you, journal as in accounting system. Actually that matches most systems I have seen. I.e., a source document field and then a source document type field. For other systems, I have seen a column for each JOIN and then business logic, maintained by database or application or both, to ensure you only have one of the three non null if that is what is needed.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36684289
I agree with mwvisa1, I often use two columns to identify the parent table.   But my your description here, it sounds like you may have more than two columns??

in the journal table I have a column named pay_to_id that points to the pay_to table that in turns points to either the employee, vendor, or biller table. Its ugly.
pay_to - table
pay_to_id, pay_to_type_id, specific_id


I would use

  PayToTable --- the name of the table (employee, vendor, biller)
  PayToID      --- the primary key of the table (employeeID, vendorID, billerID)

To make life easier, you can always create a view journal_vw  which combines the joins of these tables with a Union, then it would be easier to use...

select ...
 from journal j
  inner join vendors v on v.vendorID = j.payToID
  where v.payTo = 'vendor'
UNION
select ...
 from journal j
  inner join employees v on v.employeeID = j.payToID
  where v.payTo = 'employee'
... etc....
0
 

Author Comment

by:aka_FATCAT
ID: 36691406
Does that mean that their respective ID's have to be unique across all three tables.

As in, I can't have an employeeID = 123  and a vendorID =123
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36707473
No, you can absolutely have the same IDs.

If you were to create a unique key in your journal table, it would be on payToTable and PayToID together...


0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36707493
Agreed. In one accounting system they use UNIQUEIDENTIFIER data type as keys to the tables so together in journal they appear unique, but it is the combination that guarantees it, especially when looking at more natural (or human readable) keys like invoice ID which may look like some other ID if there is no specific prefix.
0
 

Author Comment

by:aka_FATCAT
ID: 36707524
ok. So if I understood everything correctly.

journal(table)
pay_to_id = '123'

pay_to(table)
pay_to_id = '123'
payee_table = 'vendors'
specific_id = '567'

vendors(table)
vendor_id = '567'
vendor_name = 'pepsi'


How would I write the query for this?

0
 
LVL 5

Expert Comment

by:eridanix
ID: 36708357


SELECT J.*, P.*, V.*
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.vendors V ON P.specific_id = V.vendor_id


-- INNER JOIN can be replaced with LEFT JOIN to get data for exapmle from journal if in pay_to or vendors are not linked data

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 36709135
Not to be too picky, BUT,  I have to commment on naming convention; partially because it's unclear if you're using the primary key of the journal table or not...   Is Pay_To_Id the primary key of the journal table?   Convention says you should name the primary key using the table name with _ID (like you did with vendor).   If that's not the journal's Pkey, please let us know what it is?  
(I also renamed specific_id, because I think the name should match the other foreign key column's name)

This is the model rewritten...

journal(table)
journal_id = '123'  -- primary key of journal table

journalPayTo(table)
journal_id = '123'  -- foreign key ot journal table
payee_table = 'vendors'
payee_id    = '567'

vendors(table)
vendor_id = '567'
vendor_name = 'pepsi'


But more importantly, you are setting it up as a many-to-many relationship.   Does a journal entry really associated to more than one vendor at a time?   I am thinking that a journal entry should be associated with only one external table (one to one)... which means it would look more like this...

I say this because a journal entry is typically very specific: credit, debit, payee, amount, check number, etc...   It seems to me that if you have a join table in the middle, that table may need additional attributes.

This would be the one-to-one relationship...

journal(table)
journal_id  = '123'  -- primary key of journal table
payee_table = 'vendors'
payee_id    = '567'

vendors(table)
vendor_id = '567'
vendor_name = 'pepsi'


0
 

Author Comment

by:aka_FATCAT
ID: 36709247
gdemaria:

You're 100% accurate! That's exactly how I should have look at it.

KISS - Keep It Simple Stupid

Thank you so very much.

Now my final question is,, how SHOULD I correctly query the journal table for all entries?

0
 
LVL 5

Expert Comment

by:eridanix
ID: 36709325
SELECT *
FROM journal
0
 

Author Comment

by:aka_FATCAT
ID: 36709367
eridanix:

I was looking for something a little different.

The query must link the three tables together in order to produce the correct data.

I need it to display the specific payee name.

Telling me "vendor" - "123" is not useful.

I need it to say pepsi.

I can do this using coldfusion, but I want to know if I can do it just using sql.

SELECT *
FROM journal j, pay_to_table pt
WHERE j.pay_to_table_id = pt.pay_to_table_id

<!---Use the output to query the correct table--->

SELECT *
FROM #journal.query_table_name#
WHERE #journal.query_table_name#_id = #journal.query_pay_to_id#

Open in new window

0
 
LVL 5

Expert Comment

by:eridanix
ID: 36709454
So, if I understand you are looking for something like this:

SELECT pt.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.vendors V ON P.specific_id = V.vendor_id'
WHERE vendor_id = '123


- if pt.name is name of column in you pay_to table, where payee name is stored
0
 

Author Comment

by:aka_FATCAT
ID: 36709482
Yes,

But lets not forget that we wont know the table name "vendors" until we query the journal table for it.

It could be "vendors", or "employees" or "billers" table.
0
 
LVL 5

Expert Comment

by:eridanix
ID: 36709488
Or if you can vendor name:

SELECT V.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.vendors V ON P.specific_id = V.vendor_id'
WHERE vendor_id = '123

or all vendor's names:

SELECT DISTINCT V.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.vendors V ON P.specific_id = V.vendor_id'

-- if V.name is name of column in you vendors table, where payee name is stored

0
 
LVL 5

Expert Comment

by:eridanix
ID: 36709551
In that case, this should work:

SELECT DISTINCT V.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.vendors V ON P.specific_id = V.vendor_id'
WHERE vendor_id= '123
UNION ALL
SELECT DISTINCT E.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.employees E ON P.specific_id = E.employees_id'
WHERE employees_id= '123
UNION ALL
SELECT DISTINCT B.name
FROM dbo.journal J INNER JOIN
       dbo.pay_to P ON J.pay_to_id = P.pay_to_id INNER JOIN
         dbo.billers B ON P.specific_id = B.billers_id'
WHERE billers_id= '123''
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 36709710
FatCat, assuming you are going with the two-table approach, I would do the following.   Create a view that joins and unions the table you need, then it's all easy after that.  Just select from the view whenever you are pulling from the journals table...

I don't know the columns from your journals table, so just fill in the ones you need where indicated...

create view journal_vw as
SELECT j.(desired journal fields)
     , v.name       payee_name
     , v.vendor_id  payee_ID
     , 'Vendor'     payee_Type
FROM journal J
  INNER JOIN vendors V ON P.pay_to_id = V.vendor_id'
UNION ALL
SELECT j.(desired journal fields)
     , x.name       payee_name
     , x.vendor_id  payee_ID
     , 'Employee'   payee_Type
FROM journal J
  INNER JOIN employees x ON j.pay_to_id = x.employee_id
UNION ALL
SELECT j.(desired journal fields)
     , x.name       payee_name
     , x.biller_id  payee_ID
     , 'Biller'     payee_Type
FROM journal J
  INNER JOIN billers x ON j.pay_to_id = x.biller_id



after you create this view, try select * from journal_vw
and you will see each journal record will show the name, ID and type of the payee.  
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now