Руководство проектирования баз данных необходимо

Элемент для обсуждения, поэтому я сделал его вики сообщества.

Одна вещь, с которой реляционная модель не справляется, - это обеспечение того, чтобы SacredCow и HerdLeader, удерживаемые на уровне стада, фактически указывали на коров, которые являются членами этого стада.

Скажите, что ваши стада - Звезда и Крест.Детали для «Звездного» стада могут указывать Ригеля как Священную корову и Кастора как вождя стада, но таблица «Корова» может показывать Кастора как члена «Креста». На практике при создании нового стада вы сталкиваетесь со сценарием «курица и яйцо», когда у вас либо стадо без коров (и, следовательно, без вождя стада / священной коровы), либо корова без стада.

Альтернативная модель могла бы иметь таблицу «Корова», указывающую, является ли конкретная корова лидером стада и / или священной корой для своего стада. [В физической реализации было бы возможно для уникального ограничения обеспечить, чтобы у каждого стада была только одна корова, которая была SacredCow, и одна корова, которая была HerdLeader.]. В таблице «Herd» не было бы SacredCow или HerdLeader. . Эта модель не смогла бы обеспечить, чтобы у каждого стада были Лидер стада и Священная корова.

Оба являются моделями. У обоих есть недостатки. На логическом уровне я бы, вероятно, выбрал первое, поскольку оно более нормализовано. На физическом уровне я бы подумал, какое несоответствие вызовет больше проблем и возникнет с большей вероятностью, и выберу модель, которая лучше всего предотвратит его.

Мне нравится, что вы действительно попытались сделать это самостоятельно. На самом деле вы уже почти у цели, так что вы неплохо поработали.

Давайте посмотрим на это с точки зрения объектов/сущностей.

Сущности включают следующее:

• Коровы
• Стада
• CowHerds (вы должны связать корову с услышанным)
• CowCartoon (одна из коров является любимицей, вы всегда можете ссылаться на услышанное этой коровой, поднимаясь вверх по цепочке, используя идентификатор коровы)

Коровы

• CowID (первичный ключ)
• CowName (varchar, потенциально это может быть ключом, но но ведь у коров могут быть одинаковые имена?)

Стада

• HerdID (Первичный ключ)
• HerdName (Это также может быть первичным ключом, в конечном итоге это ваше решение)
• CowID (Внешний ключ, помните, что у каждого стада есть лидер коров, вы даже можете разделить это в другой таблице, но это того не стоит)

CowHerds

• CowID (fK к коровам)
• HerdID (fk к стадам)

Комбинация вышеуказанных полей служит первичным ключом

CowCartoon

• CartoonID (первичный ключ мультфильма)
• CowID ("любимая" корова, указывает, о какой корове фермер собирается написать мультфильм)
• Published Date (дата публикации)
• Title ....

Как упоминалось в комментариях, вы также можете избавиться от таблицы CowHerds и ссылаться на HerdID непосредственно в таблице Cows.

Часть 1.

Если верно следующее:

each Cow must be in exactly one Herd
a Herd must have a sacredCow and a herdLeader
a sacredCow for a Herd must be a Cow in that Herd
a herdLeader for a Herd must be a different Cow in that Herd

Тогда вы можете реализовать эти правила с помощью этой частичной модели:

Cow (cowID, herdID) (all mandatory columns)
- primary key (cowID)
- unique (herdID, cowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader) (all mandatory columns)
- primary key (herdID)
- foreign key (herdID, sacredCow) references Cow (herdID, cowID)
- foreign key (herdID, herdLeader) references Cow (herdID, cowID)
- constraint (sacredCow != herdLeader)

Обратите внимание, как отношения FK включают в себя herdID, а не только cowID. Это гарантирует, что только коровы в стаде могут стать священными коровами или вожаками этого стада.

Такой дизайн немного усложняет реализацию, но не делает невозможным. Внешние ключи в Herd нужно было бы сделать откладываемыми в базе данных, такой как Oracle, поскольку нам нужно иметь возможность вставлять строки для стада, прежде чем мы сможем вставить строки для коров, а для стада требуется как минимум две коровы ( священная корова и вожак).

Часть 2.

Следующая задача - реализовать следующее ограничение:

only a Sacred Cow may be featured in a Cartoon

Одним из способов сделать это может быть разделение Cows на два отдельных отношения: SacredCows и NonSacredCows.

SacredCow (sacredCowID, herdID) (all mandatory columns)
- primary key (sacredCowID)
- unique (herdID, sacredCowID)
- foreign key (herdID) references Herd (herdID)

NonSacredCow (nonSacredCowID, herdID) (all mandatory columns)
- primary key (nonSacredCowID)
- unique (herdID, nonSacredCowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader)
- primary key (herdID)
- foreign key (herdID, sacredCow) references SacredCow (herdID, sacredCowID)
- foreign key (herdID, herdLeader) references NonSacredCow (herdID, nonSacredCowID)

Cartoon (cartoonID, featuredCow) (all mandatory columns)
- primary key (cartoonID)
- forign key (featuredID) references SacredCow (sacredCowID)

(В этом дизайне ограничение (sacredCow! = HerdLeader) больше не требуется, потому что теперь это разные коровы по определению.)