CODEWITHSUNDEEP
sqldatabase-designdatabase-schemaentity-relationship
MsA
MsA

Reputation: 2979

Modeling cyclic 1:N relations in database

Is it possible to model following ER diagram as SQL database tables, especially because of cyclic 1:N relations:

Upvotes: 0

Views: 344

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

This is a rare design, but it's possible.

An example: Your database contains websites. Per website there are hired authors that write articles to be published in that website. But the websites are befriended with each other and each website links to a recommended article of another website on their home page.

  • E3 = website (website_id, domain, recommended_article_id)
  • E1 = author (author_id, author_name, website_id)
  • E2 = article (article_id, text, author_id)

The question is how to fill the tables. You would want the foreign keys to be mandatory, i.e. an article must be assigned to an author which themselves must be associated to a website which again must feature a linked article. You cannot add an article without adding an author first, and no author without a website, and no website without an article - a classic the chicken or the egg causality dilemma.

The solution is either / or ...

  • ... to make one of the columns nullable. E.g. you can insert a website without a linked article (i.e. website.article_id is nullable). Then later after adding authors and articles you'd update the website table row and set the linked article.
  • ... to work with deferred constraints, if the DBMS features them. This means you insert all rows (websites, authors and articles completly) and consistency on foreign keys isn't checked at insert time (no problem hence that the website refers to an article which isn't in the article table when the Website gets inserted). Then, only when committing the data, the foreign keys are checked and everything needed is in place.

Most often the first approach is taken, as it is rare we really want all relations to be mandatory. In the given example we could start with one website without any linked article. Only later when we add another website we can add a cross article link to each of the two websites.

Upvotes: 0

philipxy
philipxy

Reputation: 15158

This is a perfectly normal design.

eats(pet, food)
hates(person, food)
owns(person, pet)

Assign Es, Rs & 1:Ms as you please.

There is no point in worrying about some pattern you happen to notice, especially when you are inexperienced. Learn how to apply your information modeling method & how to express its designs using its diagramming method.

Upvotes: 3

Related Questions