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.
I thought of it, but for user it is a mess. Do you think it is so complicated to divide it?
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.
Yeah, maybe you are right. Maybe I have to reconsider that.
But still. Which fields should i connect with relation?
Any ideas? Guys? Please!
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?
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".
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...
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?
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.
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.
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:
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.
Thank you i'll try it tonight!