Which normalization rule does this table violate?

Question

I have a table executors with columns:

id(pk) | order_id | executor_id(ek) | execution_price | created_at | selected
1        1                 200               1893883332   NULL

So, this table contains all available executors and their price.

When executor is set the field selected is changed to TRUE. Also I wonder which normalization rule does this schema violate.

Should I create a separate table where store selected executor? Also how to set constrain on the selected to avoid selection some executors?

The executor_id refers to external table users.id. Version is: 10.4.22-MariaDB

Answer 1

Put a check constraint on selected, that enforces it to be of only one value v. Then put a unique constraint on (order_id, selected). Different rows with the same non NULL order_id but a NULL value in selected won't violate uniqueness. But different rows with v would. Another value isn't possible due to the check constraint.

CREATE TABLE executors
             ( -- other columns
              order_id integer
                       NOT NULL,
              selected boolean,
               -- other constraints
              CHECK (selected = 1),
              UNIQUE (order_id,
                      selected));

db<>fiddle