Should I create a relationship to second level child in a database, what's optimal?

Question

I have the following data strucutre:

A client can have multiple visits, each visit can have multiple orders, some orders can have no visits and are linked directly to client.

Should I link client to order in a one to many relationship? Or should I remove that relationship and create a dummy visit to store orders without visits.

What is the best performance solution to list all orders? getClient.getOrders or getClient.getVisits.getOrders

What about listing clients, will it affect the speed if clients has to many relationships?

What if Visits have order, then orders have payments and then payments have multiple invoices. In that case I'm pretty sure there is no need to make a relationship between clients and invoices.

I'm using core data (iOS) which is based on SQLite. Probably will switch to another solution as core data is very slow.

Answer 1

I'm a big proponent of modeling the real world. If there are Orders without Visits then you shouldn't be creating "dummy" visits just to shoehorn data into a structure that wasn't properly designed to match the real world. What happens when a user wants to see how many Visits were made last year? Now they have to remember to discount those "dummy" visits. You should look into premature optimization as this also seems to apply here.

I would create a foreign key from Orders to Clients and a foreign key on Orders to Visits, but with a NULLable visit_id so that the foreign key is optional. In many databases you could also create an alternate key (or unique index) in Visits on the visit_id and client_id and then have your foreign key from Orders go off of that. That way, if its an Order without a Visit then the foreign key for that row wouldn't be enforced. In SQL Server the fact that one of the columns is NULL will be enough to tell it not to enforce that foreign key, but I don't know if all RDBMSs work the same way. You might have to use indexed views or some other convoluted method to enforce referential integrity if they don't.