Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

           First diagram

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.

                                 Second diagram

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...

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Makes sense. If I add clientId to the PACKING SLIP, I am able to have different client id to the client id used in the ORDER table. Definitely I must guarantee this consistency. –  Xtreme Biker Jul 21 '13 at 12:29
add comment

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.

share|improve this answer
    
If you do denormalize, you need to maintain the client_id through triggers on all the relevant tables. It is the only way to ensure consistency no matter how the data is changed. (There is an exception for bulk inserts which can avoid triggers but peopel doing this type of thing generally know to reproduce the trigger code they are avoiding after inserting or updating the ten million records.) –  HLGEM Jul 22 '13 at 18:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.