implementing MANY to MANY relation

View previous topic View next topic Go down

implementing MANY to MANY relation

Post  samimusleh on Wed Mar 20, 2013 1:23 am

Hi everybody
we have a many-many relation between 2 tables , prepared by a third table and there is child table for one of them as :
My tables are : invhdr , invitems , vhcltrans and invvhcls
invitems is the child for invhdr
vhcltrans : contains the vehicles delivery transactions
so each invoice may be delivered by 1 or mare vehicles , also 1 vehicle may deliver 1 or more invoice items , so we use the table invvhcls to keep the records for invoice(s) vehicle(s) ( many-many )
What we need is how to display for each invoice it's items together with it's vehicles >

Thanks
Sami Musleh

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: implementing MANY to MANY relation

Post  BoxesAndLines on Wed Mar 20, 2013 7:00 pm

invvhcls is your fact table. The rest should be straightforward.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: implementing MANY to MANY relation

Post  samimusleh on Thu Mar 21, 2013 1:59 am

Thank you for your reply
Actually our fact table is the line items (the granularity),furthermore there are no relation between the invitems and invvhcls , so the returned records from the query contains each record from the invitems withe each record from the invvhcls
for example :
the invitems contains the following lines for an invoice:
1-Banana
2-Apple
3-Orange

and this invoice was delivered vi 2 vehicles :
1-Vcl-333
2-Vcl-555

there are no information about which vehicle loaded with Banana or Orange or Apple

so the result of the query will be
Banana - Vcl-333
Banana - Vcl-555
Apple - Vcl-333
Apple - Vcl-555
Orange - Vcl-333
Orange - Vcl-555

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: implementing MANY to MANY relation

Post  Mike Honey on Fri Mar 22, 2013 12:50 am

Hi Sami,

I think you need make the grain of your invvhcls more detailed, adding in the invoice lines.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: implementing MANY to MANY relation

Post  BoxesAndLines on Fri Mar 22, 2013 9:17 am

samimusleh wrote:
there are no information about which vehicle loaded with Banana or Orange or Apple

What table has that information?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: implementing MANY to MANY relation

Post  ngalemmo on Fri Mar 22, 2013 11:39 am

If you want to be able to report what items were delivered on which truck, you need to capture that information. You don't have it. There is nothing you can do from a modeling perspective to invent information you don't have.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: implementing MANY to MANY relation

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum