Посочване на каноничен запис в таблица "един към много".

Представете си, че имаме таблица с държави и таблица с градове. Една държава, разбира се, може да има много градове, но един град може да бъде само в една държава, така че връзката "един към много" има интуитивен смисъл:

countries
| id | name    |
|  1 | Lorwick |
|  2 | Belmead |

cities
| id | country | name        |
|  1 |       1 | Marblecrest |
|  2 |       1 | Westacre    |
|  3 |       2 | Belcoast    |
|  4 |       1 | Rosemarsh   |
|  5 |       2 | Vertston    |

Но в допълнение към връзката ни едно към много, ние искаме да опишем връзката едно към едно на националните столици. Ако това има значение, приемете, че столиците може да се променят доста редовно и в този смисъл градовете се появяват и изчезват по желание, и че градовете могат да сменят държави. Въпросът е, че тези данни са нестабилни.

Виждам няколко опции:

  1. Добавете int колона capital към countries, която не може да бъде нула. Професионалисти: винаги точно един град; Против: не е свързано с града, нищо, което налага града, не е в страната или че дори съществува.

  2. Добавете булева колона capital към cities, която, ако е вярна, показва, че градът е столица на асоциираната държава. Професионалист: директно свързан с въпросния град, без дублиращи се колони, показващи йерархия; Против: почти сигурен, че това е лоша нормализация, тъй като нищо не пречи да има нула или повече от една „столица“ за дадена държава.

  3. Създайте допълнителна таблица capitals с колони country и city и уникално ограничение за двете колони (или поне за city). Професионалист: усеща се по-чисто, лесно свързване на countries или cities; Против: все още не гарантира, че градът е в страната или че съществува.

Кой е най-нормализираният и/или най-добрият начин за представяне на тази връзка? Има ли някакъв начин да се гарантира, че всяка държава има точно една столица, която в действителност съществува и се намира в тази страна? Предполагам, че не е възможно, в който случай, как най-добре мога да минимизирам проблемите за моя клиентски код?

В момента използвам SQLite, но се интересувам от обобщени отговори, независимо от основната база данни.

Разрових се малко и открих Посочване на първичен/запис по подразбиране в база данни, но аз не мисля, че това наистина отговаря на въпроса ми.

PS: Не е толкова лошо, ако няма столица (може да няма градове!), но би било лошо, ако има няколко.


sqlite database-design database-normalization
person dimo414    schedule 16.08.2012    source източник

Отговори (3)


arrow_upward
1
arrow_downward

Мисля, че изискването "всяка държава има точно една столица" противоречи на изискването "градовете се появяват и изчезват по желание". Ако един град може да изчезне, следва, че една столица също може да изчезне.

Можете да наложите ограничението „всяка държава има [нула или] една столица, която в действителност съществува и се намира в тази страна“ с ограничение за външен ключ върху таблица със столици.

create table capitals (
  country_id integer primary key,
  city_id integer not null,
  foreign key (country_id, city_id) references cities (country_id, city_id)
);

В тази таблица ограничението за първичен ключ гарантира, че не може да има повече от една столица на държава. Ограничението за външен ключ гарантира, че избраният от вас капитал съществува в страната, която сте избрали. В референтната таблица (таблицата "градове") също се нуждаете от уникално ограничение за {city_id, country_id}; тъй като {city_id} е уникален в таблицата "градове", {city_id, country_id} задължително ще бъде уникален и в тази таблица, така че това не е проблем.

Декларативният „начин“ за гарантиране на връзка едно към едно между държави и столици (а не връзка едно към нула или едно) е да се използва твърдение. Но не знам за текущи SQL dbms, които поддържат CREATE ASSERTION. Това ни принуждава да разчитаме на едно или повече от следните:

  • задействания и евентуално отложени ограничения,
  • код на приложение, или
  • административни процедури.

(Първоначално ще трябва да въведете ред в трите таблици „страни“, „градове“ и „главни стойности“ в една транзакция, за да удовлетворите всички ограничения. Мисля, че ще ви трябват отложени ограничения за това, но днес още не съм пил кафе.)

person Mike Sherrill 'Cat Recall'    schedule 16.08.2012

arrow_upward
0
arrow_downward

За яснота и простота бих добавил булевата колона IsCapital към таблицата с градовете. След това добавете тригер, който настройва всички други градове (които споделят държавата на актуализирания запис) IsCapital = false, когато IsCapital е зададен на true за запис. Това ще се справи с повечето ви притеснения. Единственият случай, за да се гарантира, че има точно една столица на държава, всъщност не е възможен, можете да гарантирате, че има 0 или 1, но тъй като таблицата с градове има FK ограничение за държави, винаги ще има момент във времето, в който добавените държави няма да имат градове, които могат да бъдат определени като столица.

FWIW, мисля, че логиката трябва да бъде оставена на приложението, референтната цялост на базата данни.

person JeremyWeir    schedule 16.08.2012
comment
Току-що потърсих подобни въпроси и намерих този отговор stackoverflow.com/a/638947/45767 (Отговорът му потвърждава защо Мисля, че логиката принадлежи на приложението) - person JeremyWeir; 16.08.2012

arrow_upward
0
arrow_downward

За да сте сигурни, че има точно една столица на държава и столицата не е град от друга държава, направете следното:

въведете описание на изображението тук

Обърнете внимание как използваме връзката идентифицираща, за да мигрираме COUNTRY_ID към PK на CITY, така че да може да бъде мигриран обратно към CONTRY - това е, което гарантира, че една столица трябва действително да принадлежи на държавата, на която е столица.

Циркулярната препратка тук предотвратява вмъкването на нови данни, което се разрешава с помощта на отложени външни ключове, ако СУБД ги поддържа. В противен случай можете просто да оставите COUNTRY.CAPITAL_NO NULL-способен (и да наложите неговата евентуална не-NULL-ност на ниво приложение).1

1 Това предполага, че СУБД има MATCH SIMPLE външни ключове (т.е. FK се игнорира, ако някой от неговите компоненти е NULL). Ако СУБД поддържа само MATCH PARTIAL или FULL (като MS Access), нямате късмет и ще трябва да емулирате FK чрез недекларативни средства (тригери или код на приложение).

person Branko Dimitrijevic    schedule 21.08.2012