Does this setup look OK?

[Patron Saint of Chimichangas]

I've just set up a really big database (well, for a newbie, anyway). Before I go too far with it, I'm wondering if any of you experienced users could look at my relationships window and tell me if I've made any errors that will cause problems down the road? My grasp on relationships is a little shaky, but I *think* I've got it set up right.

I've attached a gif for anyone who has a second to look it over.

Thanks in advance!

[Ender]

It's hard to say since we don't know how the business that uses this works, and what their requirements are.

One thing that stands out to me is the number of similar fields in the ShippingInformation table and InvoicingInformation table. Anytime you find yourself adding multiple "ShipDate1, ShipDate2, ShipDate3, etc.", you should consider adding an additional related table for those, then use a portal to add multiple items.

[Patron Saint of Chimichangas]

Thanks for the reply, Ender. Your comment about similar fields is well taken. I see now how a portal would work better for that.

I'm building this db for a design company. In simplest terms, a new project comes in, and all the specs are entered into the ProjectInformation table. From there, we might provide an estimate (details of which are entered into the InvoicingInformation table), or we might just start working on it. Time would need to be tracked (TimeTrackingInformation) and broken down by activity. In the end, actual numbers are entered into the invoicing table (so we can compare estimates to actuals).

[xochi]

One thing that stands out to me is the number of similar fields in the ShippingInformation table and InvoicingInformation table. Anytime you find yourself adding multiple "ShipDate1, ShipDate2, ShipDate3, etc.", you should consider adding an additional related table for those, then use a portal to add multiple items.

Agree wholeheartedly. If you ever find a table with multiple copies of a field (or field set) like that, you are designing a non-normalized table. I will come to back to haunt you later.

When I teach database theory, I remind people that there are some cases where this is ok, but they typically tend to be instances where the # of occurrences is fixed and defined by nature. For example, since most humans have 5 fingers maximum per hand, having a database with fields like "Finger1Length, Finger2Length... Finger5Length" might be a reasonable shortcut.

But in your case, unless there is some reason why there will be 5, and only 5 ship dates, you are asking for trouble.

[Himitsu]

Also, where is the center of your DB? I have found if you find the center, being, the table that is used the most, ie, customer's table, then the information should flow from that. I see that you have the product information table a kind of center.

It all depends on the people using the DB and where they do most of their work. If it is just for creating invoices or shipping records, then have that the center. But the use of something powerful like filemaker for this, why not harness more relationships that you use to view information say on the customer being diplayed on the table you use the most. Say like all the other invoices that same customer has... stuff like that. The sell point to these DBs are the power of information and how quickly you can move around to get what you need.

As for what the others said about all those fields being shippingDate ect, if it is used so much, make another table that will use a portal.

[Himitsu]

oh sorry... one more thing... your vendor information in the invoices is also not a good idea... you will be entering that information a lot. Make another table for vendors with a portal from the invoices. You can also make a valuelist that looks up the vendors from the field in the vendors table. Then, as you enter in a vendor the next time an invoice is made, and has the same vendor, it will show on the list. But if it is a different vendor, then it will be added to the valuelist. This is very helpfull because I am sure you only use a dozen or so vendors for your customers. It is painstakingly hard to keep entering that information over and over again. Remember, ease of use is why we design these things.