Зачем именно нужны связи в бд?

Question

[hrnsywtfczlh]

Я научился создавать среднего качества сайты на php+mysql.
Одна тема проходит как то мимо меня, и я хочу ее восполнить, но не понимаю как именно загуглить/сформулировать.

Mysql это реляционная база данных. Relations - отношения. В моих же структурах отношений никаких нет, и ведь это косяк?

Положим, есть юзеры и сообщения у них в чате:

User
id
login
password
status

Message
id
userid
message

Нужна ли мне здесь связь, т.е. наверное составной ключ в Message из id + userid? Например при условии что я не хочу (не нужно по требованиям) чтоб при удалении юзера удалялись его сообщения. Зачем она тут тогда?
Сами связи ведь нужны как одна из форм нормализации бд. Как тогда вообще глобально повлияет на что либо создание здесь связи один ко многим при описанных условиях?

Comments

[Константин Б.]

А как вы собираетесь соотносить сообщения с теми кто их отправляет? По какому принципу вы их храните?

[hrnsywtfczlh]

Константин Б., у юзера есть id, а у сообщения есть userid. не соотношение? кажется вы невнимательно прочитали

Answer 1

[mayton2019]

Нужно поговорить об аномалиях. Например в твоей системе я могу (теоретически) добавить месседж
который не принадлежит ни одному пользователю системы. Я просто сделаю

insert into message(9999999, -1, "Mua-haha...");

И у меня есть пост от анонимоса который не зарегистрирован как пользователь.

Разумеется можно полагаться на логику твоего приложения и думать что такая ситуация невозможна
но с точки зрения БД она вполне возможна потому как родственная связь User + Message нигде не объявлена.
И SQL позволяет это сделать.

Чтоб поправить ситуацию надо эту связь добавить и тогда я не смогу создать фейковые посты от анонимосов.

ALTER TABLE Message
ADD FOREIGN KEY (userid) REFERENCES users(id);

По умолчанию констрейнт создается с опцией restict (это было в Оракле как в Майскл - не знаю)
и это гарантирует что невозможно также удалять родительские записи пока есть дочки.
Для скорости ссылочные ключи всегда - индексированы.

Рассуждать на тему вреда от аномалий - это просто терять время. Каждый владелец БД сам решает
какие уровни строгости ему вводить. Вообще любая теория касаемая БД - по сути просто развивает
идею строгости НФ1,2,3,4,5,6 и ссылочных ограничений.

Будет ли виден пост от анонимосов - это тоже другой вопрос и он не имеет отношения к обсуждаемой
теме. Ведь тема касается именно логичности данных в БД а не тем методам которые их отображают.

По сути вопрос сводится к тому как не создавать мусор в БД.

Comments

[hrnsywtfczlh]

Довольно логично, спасибо! То есть, вопрос, в целом филосовский - как правильно хочешь чтоб было сархитектурено, так и архитектурь.
Как раз правильно делать и хочется, ибо вдруг в будущем проект любой гипотетическии будет успешен - хочется чтоб не пришлось рвать на голове волосы из за того что я забил на принципы правильной архитектуры 5 лет назад.
Стало понятнее, еще раз спасибо!

[Slava Rozhnev]

Вы путаете связи с ограничениями!

Answer 2

[Roman K]

Когда к тебе придёт менеджер и скажет: "эй, hrnsywtfczlh, а почему у нас тут вот заказов отгруженных на 15 миллионов, а получателей даже не в базе?", тогда-то ты и поймёшь, зачем нужны связи в БД. Но сперва поседеешь.

Comments

[hrnsywtfczlh]

подавится, выговаривая мой никнейм и поспешит восвояси =))
Т.е., если формулировать твой комментарий как принцип - не надейся только на логику приложения, ибо иногда она может залажать и в этот момент глобальный косяк может предотвратить как раз принцип хранения данных, верно?

[Roman K]

hrnsywtfczlh, программисты ошибаются все и часто. Поэтому сами себе пишут ловушки, в которые и попадают. Самый популярный пример - статическая типизация, начиная с самой тупой как в Си, заканчивая сложной и богатой как в Расте, Хаскеле и т.д. Потому что железкам-то вообще по барабану, что у тебя там за float и int, у железок есть нули, единички, регистры, откуда их брать и регистры, в которые их совать.

Надеяться на приложение вообще никогда нельзя. Даже если оно написано на 100% правильно, это не значит, что завтра ты напишешь что-то "там", а оно не отвалится где-то "здесь".

[Slava Rozhnev]

Вы путаете связи с ограничениями!

[hrnsywtfczlh]

Slava Rozhnev, кто именно?

[Roman K]

Slava Rozhnev, нет, я ничего не путаю. foreign key - это и есть ограничение, которое не позволяет: 1) создать кортеж, в котором есть ссылка на несуществующий кортеж 2) удалить кортеж, пока на него ссылается кто-то ещё

[Slava Rozhnev]

Roman K, Именно то что я сказал - foreign key это ограничение, а не связь (связь может быть и без внешних ключей)

[Roman K]

Slava Rozhnev, это словоблудие

Answer 3

[Slava Rozhnev]

В Вашей базе уже есть связь мезду таблицами Message и User. Поля Message.userid => User. id связывают таблицу Message с User
К Вашей схеме Вы можете только добавить внешний ключ который не позволит вставлять сообщения от несущуствующего пользователя

CREATE TABLE User (
    id INTEGER NOT NULL AUTO_INCREMENT,
    login VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    status INTEGER NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE Message (
    id INTEGER NOT NULL AUTO_INCREMENT,
    userid INTEGER NOT NULL,
    message VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (userid) REFERENCES User(id)
);

https://sqlize.online/sql/mysql57/35f0f9087119cc77...

Comments

[hrnsywtfczlh]

Но при том удалить юзера не удаляя его сообщения, как я понимаю, (по гипотетическому глупому бизнес требованию) при этом будет можно?

[Slava Rozhnev]

hrnsywtfczlh, В моей схеме - нет!

[DollyPapper]

Slava Rozhnev, в вашей схеме не понятно, можно или нельзя оставить сообщения, потому что я не вижу экшена ON DELETE.

[Slava Rozhnev]

DollyPapper, в моей схеме понятно что внешний ключ не даст оставить сообщения без пользователей и ON DELETE отношения к этому не имеет!
Единственное что сделает ON DELETE - он может удалить сообщения при удалении пользователя!

[DollyPapper]

Slava Rozhnev, action ON DELETE указывает, что делать при удалении записи, но я думаю вы это знаете. При вашем объявлении мне лично не понятно, какое действие будет при удалении, SET NULL, SET DEFAULT, CASCADE? Почему же это не влияет, когда влияет.

[Slava Rozhnev]

DollyPapper, В моем случае не будет НИКАКОГО действия! Проосто база данных не позволит удалить пользователя у которого есть сообщения. Это и называется целостностью данных.

[DollyPapper]

Slava Rozhnev, да, my bad, всегда указываю действие при update и delete и уже забыл, что если они не указаны, то база просто не даст удалить, выдав ошибку.

[Slava Rozhnev]

DollyPapper, Ну вот и хорошо! Очень рад, что мы пришли к единому мнению

Answer 4

[DollyPapper]

Вы перепутали связи и ограничения в БД. Неформально связи как раз и закрепляются ограничениями типа внешний ключ, но в целом связи это вопрос формализации моделируемого мира в контексте вашей БД. У вас уже есть связь между сообщинем и пользователем. Тот факт что у вас в сообщении имеется колонка userid как раз и есть факт связи, что две сущности: сообщения и пользователи связаны. Однако эти связи принято подкреплять на уровне самой БД теми самыми огланичениями. как указал mayton2019 я могу в вашей системе в теории создать сообщение которое не принадлежит ни одному пользователю в вашей базе. Рассматривайте ограничения как штуки которые не позволяют нарушить логику ваших связей.
Еще почитать на тему проектирования связей

Answer 5

[Vitsliputsli]

Как уже правильно написали, ваш вопрос больше не про связи, а про ограничения (связь у вас присутствует: message.user_id -> user.id). Чаще всего, для контроля целостности базы данных используют ограничения на стороне этой же базы данных. Вы можете сделать этот контроль и в приложении: в этом простом случае, при удалении записи в user нужно будет чтото делать и с зависимыми записями message и все это нужно будет описывать (а ведь связь может быть не прямая, но и через другие записи). Когда база разрастется, появится много новых и сложных связей, вам придется все это контролировать, причем если вы забыли добавить контроль нового элемента вы сразу можете и не заметить, что консистентность нарушена.
При определенных условиях контроль действительно переносят в приложение, но когда понимают чем рискуют, а выигрыш перевешивает. Но это не тот случай, добавить ограничение будет эффективнее.