Customer ID required on just Orders?

[sal88]

Hi all

 

I have an Orders table.  This is the first thing that is populated in a typical 'session'.  Orders can be, in any combination or quantity, assigned to Acknowledgements, Invoices or Delivery Notes (all separate tables).  Therefore each Order record includes the fields: Acknowledgement_IDF, Invoice_IDF and Delivery_IDF.

 

Now I also have a Customers table, my question is, would it be OK to just have the Customer_IDF field on the Orders table, or should I have that field on all the other tables as well (Acknowledgements, Invoices and Delivery Notes), making each Customer directly associated with all 4 tables, instead of just 1.

 

Any advice appreciated.

Thanks

[MarkB]

Hi Sal,

 

I am assuming that a single order can have more than one acknowledgement, more than one invoice and more than one delivery, but all associated with one customer.  If that is true, then yes I would use the customer ID in each of the other tables.  That way you could see all the deliveries for one customer without having to traverse through the history of the order.  Now on the other hand, if a single order only has one of the other three items, then I would only have 2 tables, one for customers and one for orders with a single field for the 'status' of the order.

 

Mark

[sal88]

Hi Mark

 

That's almost the case except a single order can be assigned to records in all tables (ack/delivery/invoice) but just with a maximum of one record from each table.

 

Thanks

[MarkB]

Hi Sal,

 

If you are saying that one customer record is assigned to only 1 of the 3 other tables, then that is a one-to-one relationship.  Normalization rules state that one-to-one relationships shouldn't exist (few exceptions) because the data can all be assigned to the one customer record.

 

Mark

[sal88]

Hi Mark

 

It will be the same customer for all the orders/acks/invoices/delivery notes that are linked yes.  However an ack/invoice/delivery can have multiple orders attached, but again all for the same customer.

 

I know that having the Customer_IDF only on the Orders table would be an issue because it would mean that if I were on a customer record I could only get a portal to show the Orders table, and not the other 3.  However it would be fine to get past this by just performing the appropriate search on one of the other 3 tables in separate layouts.  Would there be any other implications?

 

 

Thanks

[Rick Whitelaw]

Hi Sal,

 

If you are saying that one customer record is assigned to only 1 of the 3 other tables, then that is a one-to-one relationship.  Normalization rules state that one-to-one relationships shouldn't exist (few exceptions) because the data can all be assigned to the one customer record.

 

Mark

I couldn't disagree with you more. One to one relationships are often the best and most economical method of data storage. An example would be a Contacts table. Since everyone has an address there's nothing wrong with including it in the main Contacts table. Relatively few people have a website. It makes perfect sense to have a separate table for websites and this would be one to one. Databases reserve storage for empty fields. With a separate table for www you end up with no empty "website" fields in the Contacts table.

My two cents.

[MarkB]

Quote from John Mark Osborne: http://www.vtc.com/products/FileMakerPro9Intermediate/Relationships/67403

 

"The one-to-one relationship is very uncommon in FileMaker and I've been working with FileMaker for a couple of decades and I've seen very, very few instances of a true, really you need this type of relationship. Usually what you do is you just simply take the fields from that other table and place it into your other table so you really have one table at that point. There's really no point in relating one record to one and only one record in another table."

 

Rick, I can see your point, IF the secondary values were rare and you were wanting to avoid empty fields to keep the primary record as lean as possible.  In Sal's case, every order record will have a history that goes through the steps of Acknowledgements, Invoices or Delivery Notes.  So the only need to have a separate table for Acknowledgements, Invoices or Delivery Notes is if there will ever be more than one of each tied to a single order - in other words a one-to-many relationship.

 

Mark

[sal88]

Hi guys

 

Sorry I don't think I've been very clear.

 

Yes it would be a one to many relationship.  e.g. each Invoice can have multiple orders, all for the same Customer.

I guess the reason I'm confused is because for example, one of the ways the orders are assigned to the Invoice table is not from a layout based on the Orders table, but a layout based on the Invoice table.  Therefore, at the moment, you create a new Invoice, select the customer and then select from a value list which displays all uninvoiced Orders for that Customer.

 

I would like to just do away with the Customer ID on the acks/invoice/deliveries, but because they are a bit more 'independent', and not just records that are attached to Orders (though they wouldn't exist if they didn't have Orders assigned), something's telling me to think a bit harder.

[MarkB]

Hi Sal,

Thanks for clarifying that it truly is a one-to-many relationship.  Let me see if I have this right - A customer can place multiple orders, which then have  acknowledgements and at a later interval you create an invoice.  It sounds from your description above that you create an invoice, then look for a customer that needs to be sent an invoice.  Is that correct?  If so, I would first find customers who have uninvoiced orders and then create invoices for them.  No matter, since these tables (customer, order, acknowledgement, invoice) are related to a customer, I definitely would include the customerID in all related records.

 

Cheers, Mark

[sal88]

Hi Mark

 

Yes that is correct, I will keep the Customer ID on all tables then.

Thanks for your assistance guys.