13 Replies Latest reply on Jan 26, 2012 12:11 PM by AntonChuykin

    Q about proper relations

    AntonChuykin

      Title

      Q about proper relations

      Post

      Hi there!

      Please try to explain me about relations between tables. I have a situation:

      Order confirmation DB with fields of customer and delivery place, both are companies. Let's say delivery place is a customer of my customer.

      So, I have 3 tables:

      Invoice.table with id.invoice (serial), customer.invoice, delivery.invoice fields (many more, but don't worth mentioning)

      Customer.table with id.customer (serial), name.customer, address.customer

      Delivery.table with id.delivery (serial), name.delivery, address.delivery

      By default, I set delivery.invoice to lookup from customer.invoice (in my Invoice.table) as in 80% cases they match, but sometimes I need to add new delivery place. So, there is dropdown list that use autocomplete to fill delivery place. 

      How should I set my relations between this three tables?

      Ah! There is one more thing. Sometimes my delivery place becomes my customer, so I just need a small button and a script that will set new record in custome.table copying info from delivery.table. But i don't think i need relations for script. 

      Thank you for all your help

        • 1. Re: Q about proper relations
          Sorbsbuster

          Why not keep all delivery contact data in one table?  If you want to know which is only a delivery point and which is a customer, just add a ContactType field.

          • 2. Re: Q about proper relations
            AntonChuykin

            I thought of it, but for user it is a mess. Do you think it is so complicated to divide it?

            • 3. Re: Q about proper relations
              Sorbsbuster

              What makes it a mess?  Currently you have one table with Name, Address, Phone, Fax, E-mail, blah blah, and another table with Name, Address, Phone, Fax, E-mail, blah blah, and any of the records in one could just as easily become a record in the other (and then will they exist in both tables, or only one? And if only one, which one?), with form views, list views, find scripts, sorts, prints, - all probably very, very, similar.  So you have twice the work to maintain two tables.

              Or just add one field...

              It is what I would do when I have two tables that are as similar as that.

              • 4. Re: Q about proper relations
                AntonChuykin

                Yeah, maybe you are right. Maybe I have to reconsider that.

                But still. Which fields should i connect with relation? 

                • 5. Re: Q about proper relations
                  AntonChuykin

                  Any ideas? Guys? Please!

                  • 6. Re: Q about proper relations
                    philmodjunk

                    We'd need to know more about your business practices as there is more than one way to set this up and some options are a better fit for some businesses than others.

                    Are all the items on a given invoice always shipped to a single delivery address? Or could it be split to two or more recipients?

                    • 7. Re: Q about proper relations
                      AntonChuykin

                      Dear Phil,

                      Sometimes it is 2 unloading places, sometimes even 3, but not more. But I don't need to split invoice's specification. It is enough if I just name name destinations in delivery adress field. Or, I even can put smth like "several delivery addresses there".

                      • 8. Re: Q about proper relations
                        philmodjunk

                        That's not really the point of the question. If you have to deliver to more than one location, you have to both be able to select multiple delivery points and also to designate which items on the invoice are to be delivered to each such location. Some businesses will do this by generating separate invoice records for each delivery address. Others will set up a "shipment" or "delivery" table where each record is to a specified delivery location and the lineitem records from the invoice are split between each "shipping tag". Which works best for you is more of a business decision than a database design decision as you can set up your system to support either practice--but the structure will be different in each case.

                        The LineItems table typical of almost all invoicing systems in FileMaker and other relational databases, is not listed in your original post so that might be the first change you need to make here...

                        • 9. Re: Q about proper relations
                          AntonChuykin

                          No-no-no, what you are describing is way too complicated. In my case I just need to show who is the customer and where is it going to be delivered. I need to somehow use 2 fields with more or less same information inside. Once again:

                          I have three tables:

                          OrderConfirmation.table with id.ordconfirmation, customer.field, delivery.field, etc...

                          Invoice.table with id.invoice, customer.field, delivery.field, etc...

                          Customer.table with customername.field, customeraddress.field

                           

                          I need to know how do they communicate within each others? what fields should be connected? Maybe you have examples? 

                          • 10. Re: Q about proper relations
                            philmodjunk

                            Good luck with that.

                            It may seem too complicated, but I don't see how you can do what you want with just the tables you have listed. With just the tables you've listed, there is no simple way to manage an invoice that lists multiple items. You do have invoices that list more than one thing don't you? While you can do that without a line items table, your design will be more complex, not simpler.

                            Without a way to track which items on an invoice go to each delivery location, you have no way to manage the delivery process--which I thought was the main point behind your question here.

                            • 11. Re: Q about proper relations
                              AntonChuykin

                              Dear Phil,

                              Of course I have lineitem in my invoicing system and it is all more complicated. I just don't want to distract on it. And I don't need to track my delivery. I just need to show my customer that we do know, that there are several unloading places (which happens not very often). But if it is happening, we use one of the fields in portal row (Notes field) to show where the goods are going (shortly, ex. DE74, where DE is germany and 74 is first digits of postal code). Please check picture attached.

                              • 12. Re: Q about proper relations
                                philmodjunk

                                I don't really see the purpose of separate tables for OrderConfirimation and Invoice. Isn't there just one OrderConfirmation for every invoice? And note how you have the same fields in both tables here: customer.field, delivery.field... Doesn't that require you to enter the same exact info twice? (but I could be missing something there...)

                                Just looking at Customer contact info, delivery contact info and Invoices for the moment, you'd have:

                                Customers-----<Invoices-----<DeliveryList>-----DeliveryContacts

                                Customer::CustomerID = Invoices::CustomerID

                                Invoices::InvoiceID = DeliveryList::InvoiceID

                                DeliveryContacts::CustomerID = DeliveryList::CustomerID

                                Customer and DeliveryContacts would be occurrences of the same table. CustomerID in that table would be an auto-entered serial number--not a customer name.

                                • 13. Re: Q about proper relations
                                  AntonChuykin

                                  Thank you i'll try it tonight!