sql structure of client/supplier satisfaction survey

Question

I need to build a form to send to several clients. Each client will answer 5 questions, relative to their current suppliers. They will be able to select the suppliers they are working with from a dropdown list and, for each supplier, the client will answer each question ticking a radio button number from 1 to 5 depending on the quality of the service they receive from the supplier. The question are the identical for every supplier.

How to build the Sql table structure for this?

I thought about building two tables, clients and suppliers. The client table has a client per row, which has one column with the name of the client and the other with (an array?) the suppliers they have.

The suppliers table, instead, would be a supplier per row with its name and the 5 questions and the relative answers as columns. The Client table would have the foreign key in the suppliers column, that would communicate with the suppliers table.

The problem is, every client has different entries for the questions column of the suppliers so I don't know how to differentiate the single supplier for each client who will fill the form.

How would your approach be on this?

Thanks in advance

Answer 1

Here is a simple ERD of how I would do that:

Explanation

The tables you already have:

• The Client table stores data directly about client.
• The Suppliers table stores data directly about the supplier.

Then I've added:

• A dedicated Questions table allows you to decouple the questions from the DB schema. You can add/remove questions at will in the future as rows in this table.

Lastly, instead of adding more columns, relationships are given their own tables. Don't be afraid of having more tables.

• The ClientSuppliers table records the links between a Client and a Supplier. This will help determine who to send survey questions to about which supplier.
• The ClientSuppliersAnswers table records the response of a specific client to a specific question about a specific question (note the 3 FKs).