Getting related foreign keys from parent entities

Question

I'm thinking about a design issue which affects my project's data base. Supposing there are three diferent tables:

• CLIENT
• ORDER
• PACKING_SLIP

---

Each order has its client and different packing slips. So there are some foreign keys which are compulsory, there would be clientId for the ORDER and orderId for the PACKING_SLIP table. That makes full sense.

           

Now suppose in my logic I want to have access to the client from the packing slip. As I'm using an ORM tool as Hibernate, that involves firstly accessing the order from the packing slip and after getting the client from it.

If I want to have access to the client directly, I should add the clientId foreign key also to the PACKING SLIP table.

                                 

My question is, is that a correct design if there's a possibility to get the client joining the ORDER table? Isn't it a bit redundant? I think it's a control problem and the data base part shouldn't take care about it...

Answer 1

That's bad design.

Why? Not only it is redundant, but also allows for inconsistencies, i.e., insert, update or delete anomalies.

You could have a packing slip pointing to a client which is different to the one its order points to.

If that happens, a query that joins through ORDER will fetch a different customer that a query that doesn't.

That's because you are violating 2NF.

If you want to avoid having to join through ORDER every time, create a view and select that view. The view still have the join but you will not see it.

In non-transactional databases like data warehouses, you don't have to respect normal forms, but in an OLTP databases like yours seems to be, you will have a lot of headaches if you don't. Obviously there are exceptions, but that's not the case with CLIENT, ORDER and PACKING_SLIP.

Answer 2

Generally you'd do exactly what you've described -- have clientId in ORDER and orderId in PACKING SLIP, and join the two tables to geth the clientId for the PACKING SLIP. Unless you have a really performance critical application, or unless you have a large number of orders, packing slips or clients, this is not a problem.

If you're really worried about performance, there is nothing wrong with adding clientId to the PACKING SLIP, though that would require adding code (or other logic) to ensure that clientId in PACKING SLIP will stay consistent with clientId in ORDER. This may be best in situations when the application frequently needs to access clientId from PACKING SLIP without getting a corresponding ORDER and if you will be updating clientId in ORDER rarely.

Ideally this would be done using a stored procedure on the DB side, where updating an ORDER or adding a PACKING SLIP would be done through a stored procedure that will enforce the consistency of clientId, but I'm not sure whether your ORM allows that. Alternatively, some ORMs provide a way to add such logic through code.