0

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

MsA
  • 2,599
  • 3
  • 22
  • 47
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Dec 17 '19 at 23:12
  • 1
    Why shouldn't this be OK? It says there's 3 relationships that happen to each involve different pairs of entities. eats(pet, food), hates(person, food), owns(person, pet). What exactly do you mean by "cycle" & why do you think that's a problem? If you don't say why there is a problem, what can we say other than that that diagram says what your particular design method & diagramming style says it says? You don't even try to find an example that is a problem or isn't. – philipxy Dec 17 '19 at 23:17
  • 1
    See my last comment on the answer by Thorsten Kettner, which is talking about a problem that is not what your diagram describes. It is talking about constraints that could hold in a situation like your diagram describes, but it isn't caused by what your diagram describes. Also see my other comments there. – philipxy Dec 18 '19 at 09:47

2 Answers2

3

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
0

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    The relationships in the question are binary. They can't form a FK cycle on one entity. This answer is not a case of the question. – philipxy Dec 18 '19 at 00:10
  • @philipxy: They are 1:n relations. What is a binary relationship? – Thorsten Kettner Dec 18 '19 at 00:13
  • 1
    You don't seem to understand how to read a Chen true ER diagram (as the question's apparently is) or associated standard mathematical terminology. Diamonds are relation(ship)s/associations as in Entity-Relationship Model & Relational Model. A binary relationship is a relationship in which 2 entities/values participate. FK constraints are wrongly called "relationships" by methods that wrongly call themselves ER. They are facts. Or instances of a meta-relationship on tables & column lists. See my answer & our comments [here](https://stackoverflow.com/a/46029100/3404097). – philipxy Dec 18 '19 at 00:22
  • @philipxy: Ah, this is why your name sounded familiar :-) Well, I may see this too simple, but I know Chen diagrams being used to model a database. In an RDBMS an n:m relationship would be modeled with a bridge table, a 1:n relationship with a foreign key. It may happen that the resulting database is not as good as the diagram (it's hard to enforce {1}:{1,n} relations for instance in a RDB, while {1}:{0,n} relations are easy peasy), but when I use Chen I only do it with the purpose of building a database in the end, so I even consider it fortunate to think in database terms when drawing. – Thorsten Kettner Dec 18 '19 at 00:32
  • 2
    In a Chen diagram all relation(ship)s/associations of arity >1 are represented by diamonds with a line per participant. So you are not using terminolgy in a way consistent with it & your design is not consistent with the given diagram. (And Chen diagrams classically don't address partial vs total participation.) (Lines do map to FKs.) PS Tables alone represent relationships. Constraints don't. Relation(ship)s/associations are not modeled by FKs. However a FK constraint is associated with a certain binary relation(ship)/association associated with a projection of the table the FK is in. – philipxy Dec 18 '19 at 00:53
  • 2
    The constraints involving simultaneous updates you describe/assume are due to constraints involving the 3 relation(ship)/associations that the given diagram has nothing to say about. All it says is that entities participate in the relation(ship)s/associations, ie that there are FKs from the relation(ship)/association tables to the entity tables. Moreover the 1:Ms in the diagrams talk about each of the 3 relation(ship)/association tables & have nothing to do with the FKs or with FKs being called "relationships" or "1:M". – philipxy Dec 18 '19 at 09:42