0

I'm kinda new here, today I got a test in which I had to organize a database respecting the 3 normal forms. Here is a type of E/R diagram: 1

I don't know how to normalize the complaints part, because the text says that a complaint has to contain the email of how registered it and the date, and it can refer to a shop, or to a service center. In that case, it needs the service center id and the model id.

I thought that i could make a table Complaints that had 3 foreing keys: the id of the shop, the id of the service center and the id of the model. Doing so, I would set to null the shopID if the complaint is reguarding the service center and the model, and otherwise set the service center ID and the model ID to null if it is reguarding the shop.

Let me know if you have an idea to solve that. Thank you very much.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
mattberna
  • 1
  • 1

1 Answers1

0

Yes, I would recommend what you propose. Use two different columns, and set one or the other to NULL. That's usually the most straightforward solution.

There are a few other alternatives, which you might use to support specific queries. I described some in my answer to Possible to do a MySQL foreign key to one of two possible tables?

I also cover this in a chapter of my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828