по мне не норма по всем вопросам, изменения надо писать и логически и физически в отдельную таблицу

А если в моем случае есть таблица user с пользователями. И таблица specialist с сотрудниками.
И как мне указывать в таблице message от кого эта запись?
Создавать 2 поля с user_id и spec_id. И всегда только 1 будет заполнена?

Дело не в объемах, а в логике организации данных. Если нет какой-то веской причины хранить данные вместе, то нужно делать отдельную таблицу, потому что в пользу отдельной таблицы в общем случае эти причины есть: это производительность, эффективная утилизация ресурсов СУБД, обслуживание. Да в общем и запросы писать проще будет.

Я бы писал в ту же таблицу, анулировал бы старый заказ и создавал бы новый, как копию.
В отдельную таблицу это копировать практически нет смысла

Наоборот.

Вот сейчас у меня так и сделано, но руководство настаивает на таблице с архивом.
Нужна аргументация за оба варианта.
В инете не нашел норм анализа
Поэтому к сообществу обратился

Нет смысла. Доступ по индексу -- O(log N), это практически не зависит от объёма данных.
У тебя с разделением и без будет разница в объёме максимум в два раза, это под логарифмом -- ничто.

А так у тебя будет одна сущность, одна работа с ней -- редактирование и всё прочее -- куча экономии, это намного важнее.

Да, я тоже так же рассуждал...
Но идея с архивной таблицей тоже выглядит логично.

Идея с архивной таблицей лет 30 назад была логична, в клиппере...

Аудит доступа проще настраивать при реализации отдельной таблицы на архив.

Ну... аудит вообще сложная и отдельная тема. Не вижу чем проще.

Ну и аудит реально очень редко нужнен.

Доступ по индексу в смысле к конкретному заказу? А к списку заказов, сканом? На таких мелких объемах, конечно, не будет заметно, да.

Но в целом не вполне понятно, в чем именно профит делать архив в той же таблице? Редактирование одной сущности - но архив и текущие данные разве являются одной и той же сущностью? Паттерны работы разные, архив не редактируется, а изредка смотрится, заказы редактируются конкурентно. И всё прочее - что именно 'прочее'?

блин, что за поток вредных советов. человек хочет логировать изменение атрибутов в таблице, какой блин 30 лет назад, нужны дополнительные поля (кто изменил, когда, возможно причина), их тоже добавлять в основную таблицу? по опыту работы самая большая таблица в базе в процентах 60 - это таблицы логирования, из-за того что кто-то в интернете советуют все хранить в оснонвой таблице прикрываясь логарифмом, теперь и бэкапы должны быть в несколько раз больше и DBCC CHECKDB в 2 раза дольше идти? без разделения логов и актуальной инфморации построить нормальную архитектуру в современном IT нельзя

У тебя по-любому доступ по индексам каким-то должен быть.

Ну, не знаю, как-то всегда строил без разделения, никогда особых проблем не было.
Про логи ты правильно написал.

я свое мнение высказал, времени разжевывать нет, внимательно думайте и решайте сами, ну и реализацией не затягивайте, лучше сразу сделать что-то хоть и плохо, чем сделать идеально никогда

а можно подробнее описание кейса и объем данных? в случае хранения всего в одной даже получение актуальной записи уже головная боль для разработчика, вводить доп столбец с IsAtive=True или как?

Так это не ко мне вопрос, к автору вопроса.