 | |
Concept of Many-to-Many
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Concept of Many-to-Many -
05-17-2007
, 07:40 PM
#2
| |||
| |||
|
|
I have been trying to wrap my brain around the practical side of Many- to-Many relationships. I want to layout out a scenario. I am setting up a food & beverage inventory application. I have product in one table. I have a table of storeroom locations. As I understand it the join table needs minimal fields. I am thinking 3 fields. A serial number, ProductID and LocationID. Working from the Product Table, I add a new product. Using a portal I add Join-Table records for each location. End result is that there are a Product.count times Location.count records in the Join-Table. I can generate all kinds of reports from the Join-Table using related fields. Does this cover it? Have I missed anything in terms of the concept application? TK |
That presumes each product has a unique ID (one), and quantities of
each product can be stored at more than one location (many). The
join is useful because you can edit your data in one place and have
the change show up in all related records.
Your product table can store various data about each product, and your
location table can store data about quantities of each product at each
location. End result could be a layout based on the products table,
with a portal linking it to the location table through a join table so
that you could display product quantities per location.
Actually the setup you're going for might be more complicated than you
need if I understand the goal correctly, unless you frequently edit
the various attrubutes of your products and/or locations.
Hope this helps.
--
FW
#3
| |||
| |||
|
|
I have been trying to wrap my brain around the practical side of Many- to-Many relationships. I want to layout out a scenario. I am setting up a food & beverage inventory application. I have product in one table. I have a table of storeroom locations. As I understand it the join table needs minimal fields. I am thinking 3 fields. A serial number, ProductID and LocationID. Working from the Product Table, I add a new product. Using a portal I add Join-Table records for each location. End result is that there are a Product.count times Location.count records in the Join-Table. I can generate all kinds of reports from the Join-Table using related fields. Does this cover it? Have I missed anything in terms of the concept application? TK |
look at an Invoicing system. When understood how it is built, then apply it
to your case.
Invoicing needs a Customers table - one record per customer identified by a
unique CustID -, a Product table - each product available to be sold is in
one record with its own ProdID -, then an Invoices table - with each invoice
= one record and one InvID -.
Now you know that a given invoice can be made of many products, and that a
given product can be seen in many invoices. So the quick answer is that you
need an extra table named Lines Table in between. This Lines table will have
one record per couple InvID-ProdID. For example if a given Invoice has to
have 3 products, then you create 3 records in the Lines table. From then on,
the work - editing, etc. - will be easier to be made in the most detailed
table ie the Lines table.
Is my description enough to see what is given and what is to be filled up ?
Probably not, while it should be. My advice would be either to dissect an
existing Billing DB or - my choice - to try to create your own. You'll then
easily will see what IDs you need to carry between tables (ie InvID or
CustID) and the IDs you have to fill up say 'by hand' - or by lookup
tables - like the ProdIDs.
Come back here for additional questions if you need it.
Remi-Noel
#4
| |||
| |||
|
|
The easiest way to understand the many to many relationships concept is to look at an Invoicing system. |
Quote:
|
When understood how it is built, then apply it to your case. Invoicing needs a Customers table - one record per customer identified by a unique CustID -, a Product table - each product available to be sold is in one record with its own ProdID -, then an Invoices table - with each invoice = one record and one InvID -. |
Invoices have many line items - one to many
products have many line items - one to many
Invoices to products (by way of line items) is many-many. Lineitems
acts as the join table between invoices and products, but its not
really a text-book many-many relationship because lineitems are
complete entities in their own right.
I think a better example to get started with many-many would be
something simpler and more self-contained, where the join table is
literally a 2 column list of pairings.
Suppose for example you wanted to track where people would like to go
on vacation. you'd start with two tables:
people, and destinations. Each person may want to go to several
destinations, and each destination may be selected by many people.
So in that case you'd create a join table {personid, destinationid}
that lets you link people and destinations. Indeed its the only way to
do it.
-cheers,
Dave
Quote:
|
Now you know that a given invoice can be made of many products, and that a given product can be seen in many invoices. So the quick answer is that you need an extra table named Lines Table in between. This Lines table will have one record per couple InvID-ProdID. For example if a given Invoice has to have 3 products, then you create 3 records in the Lines table. From then on, the work - editing, etc. - will be easier to be made in the most detailed table ie the Lines table. Is my description enough to see what is given and what is to be filled up ? Probably not, while it should be. My advice would be either to dissect an existing Billing DB or - my choice - to try to create your own. You'll then easily will see what IDs you need to carry between tables (ie InvID or CustID) and the IDs you have to fill up say 'by hand' - or by lookup tables - like the ProdIDs. Come back here for additional questions if you need it. Remi-Noel |
#5
| |||
| |||
|
|
On May 18, 8:58 pm, "Remi-Noel Menegaux" <rnmenegaux_AT_free.fr wrote: The easiest way to understand the many to many relationships concept is to look at an Invoicing system. I'm not sure that's the easiest. When understood how it is built, then apply it to your case. Invoicing needs a Customers table - one record per customer identified by a unique CustID -, a Product table - each product available to be sold is in one record with its own ProdID -, then an Invoices table - with each invoice = one record and one InvID -. So customers have many invoices -- one to many Invoices have many line items - one to many products have many line items - one to many Invoices to products (by way of line items) is many-many. Lineitems acts as the join table between invoices and products, but its not really a text-book many-many relationship because lineitems are complete entities in their own right. I think a better example to get started with many-many would be something simpler and more self-contained, where the join table is literally a 2 column list of pairings. Suppose for example you wanted to track where people would like to go on vacation. you'd start with two tables: people, and destinations. Each person may want to go to several destinations, and each destination may be selected by many people. So in that case you'd create a join table {personid, destinationid} that lets you link people and destinations. Indeed its the only way to do it. -cheers, Dave Now you know that a given invoice can be made of many products, and that a given product can be seen in many invoices. So the quick answer is that you need an extra table named Lines Table in between. This Lines table will have one record per couple InvID-ProdID. For example if a given Invoice has to have 3 products, then you create 3 records in the Lines table. From then on, the work - editing, etc. - will be easier to be made in the most detailed table ie the Lines table. Is my description enough to see what is given and what is to be filled up ? Probably not, while it should be. My advice would be either to dissect an existing Billing DB or - my choice - to try to create your own. You'll then easily will see what IDs you need to carry between tables (ie InvID or CustID) and the IDs you have to fill up say 'by hand' - or by lookup tables - like the ProdIDs. Come back here for additional questions if you need it. Remi-Noel |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.