Very bizarre that you would be concerned with your customers customers. I certainly would not be comfortable with this as your customer. Based on your description, it sounds like you need tblCustomers, tblOrders, tblOrderDetail and then mirror customers (tblSecCustomers) and orders (but not details) as tblSecOrders. However, it isn't obvious if you're keeping the customer details about those who are not your direct customer. Assuming yes, I probably would not have both in one table, identifying them by some other field. Rather secondary would mirror primary as noted above, updating both sides when necessary. Joining primary orders to secondary orders, and customer id's are in both order tables, you would be able to join primary to secondary on orders and return those customers where the data was known for both. The single details table could link to either or both Orders table (depending on the query need) and return the details of an order.
I'm kind of writing off the top of my head here, since as I said, it seems like a strange situation - bordering on invasion of privacy. Perhaps this is more common than I'd expect, and I'm wet behind the ears.
Last edited by Micron; 07-02-2018 at 08:23 PM.
Reason: added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.