I am designing a complaints module. The complaints can come from people who are not customers. Namely, people who did not become a customer but are complaining that the sales reps are too aggressive. Complaints can also come from existing customers.

Currently I have 4 tables involved:

  • Complaint
    • ComplaintReporter_Id
  • Customer
    • Contact_Id
  • Contact
    • Name
    • Phone
  • ComplaintReporter
    • Customer_Id (Nullable)
    • Contact_Id (Nullable)

I don't like the nullable columns on Complaint Reporter. When the complaint comes from an existing customer, I leave the Contact_Id null because every Customer has a Contact_Id. When the complaint comes from a non-customer, I leave Customer_Id Null.

Is there a better design to support this situation that won't require nullable columns?

I suppose I could get rid of ComplaintReporter table and add its columns directly to complaint which will make the diagram look less funky, but I'm still left with nullable columns.

enter image description here

  • 3
    A customer is a role played by a person, if that helps – Neil McGuigan Oct 20 '16 at 18:12
  • 2
    Why not have as single Contact_Id in the ComplaintReporter table? Since both customers and non-customers have a ConactId, this should work, right? – ypercubeᵀᴹ Oct 20 '16 at 19:30
  • 2
    Or even get rid of the ComplaintReporter table and have the Complaint (ComplaintReporte#_Id) to reference Contact (Contact_Id) – ypercubeᵀᴹ Oct 20 '16 at 19:32
  • @ypercubeᵀᴹ I liked the separate ComplaintReporter table in case the requirements change in a way that multiple people can report the same complaint, or if the behavior of a ComplaintReporter gets more complicated. Seems easier to do a separate entity now than later. I think I like the idea of just having a relationship to contact though and getting to Customer through there. And maybe I can put a ComplaintReporterType that marks it as either Customer or NonCustomer. – SQLSuperHero Oct 20 '16 at 20:25
  • 1
    You can find if it's a customer or not by checking if the ContactID exists in Customers. – ypercubeᵀᴹ Oct 20 '16 at 20:45

Your Answer

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

Browse other questions tagged or ask your own question.