I have an order table, and in the order table I have a contact_id.

Now, each contact is linked to an account. For my order table, should I have both contact_id and account_id?

If I wanted to say, list all orders for account ABC, I could just look through all orders where account_id = "ABC's id"

If I had just the contact_id I would have to go through all the contacts and then pull orders based on each contact_id... this seems kind of like a pain in the neck.

I don't know if this is really a normalization question, but the easy way seems a little redundant, but I'm not 100% sure. What is the accepted best practices for doing something like this?

Thanks in advance.

  • To answer your title: "Yes. Yes, it does." – Jon of All Trades Jun 6 '14 at 23:02

There are different levels of normalization. If your business logic is set up where each contact has an associated account (but more than one contact can be in an account), then there is no reason to put the account ID in the order table because you can get it by going back to the contact table. In other words, account ID in the contact table is a foreign key.

  • Ok, this is what I was wondering. I'm actually building something to add into a SugarCRM database. I need to pull the information out. The accounts and contacts are related through another table. If I wanted to list orders by account, wouldn't just having the contact_id make for more work for me, the db.. everybody/thing involved? – Dan Jun 6 '14 at 23:32
  • It's hard to say without looking at your design - you could post a database diagram. Normalization's goal is not to reduce work. It uses relations to minimize space used. It is for this reason that some organizations choose to de-normalize certain parts of their databases, to improve performance. However, in your case that is probably not necessary. – Dave.Gugg Jun 9 '14 at 13:31

The trick here is modeling the business practice. For example: Is it possible that the order fulfilling company (owner of the database) may fulfill orders from a company large enough to have departments that can independently order items without going through a single contact? Or can the business process somehow guarantee that a single contact per account will be used? If the former is the case, then include both in the orders table, as you might have, say, one IBM account, but multiple people from the company using the account. In this scenario, having only the contact_id in the orders table would suffice, and you can relate contacts to companies elsewhere.

Alternately, perhaps there's a situation where a broker is buying under multiple accounts, in which case a single contact_id in the orders table would not suffice if the contact_id is related to 1 or more companies elsewhere. The contact_id and company_id in the orders table covers both scenarios, but the business practice and domain may make this scenario unnecessary, even superfluous.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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