CODEWITHSUNDEEP
sqldatabase-designentity-relationship
kace91
kace91

Reputation: 853

How can I represent a ternary relationship in SQL modeler? Or else, what alternatives do I have?

let's say I have 3 entities. A, B and C.

A combination of A and B produces a new C element, and that element must be stored (per request).

For example, a USER (one entity) consults a PRODUCT (other entity) and that query is stored with several atributes (date, etc.)

I can only see that as a relationship (CONSULTS) that connects PRODUCT, USER, QUERY.

The database I'm designing has more than one situation like that one, and I only see that ternary relationship as a solution, yet programs like SQL data modeler won't let me represent such a relation.

What should I do then? Is there something I'm missing?

Upvotes: 1

Views: 1367

Answers (1)

Erwin Smout
Erwin Smout

Reputation: 18408

Relations (in the proper and strict meaning of the term as per "Relational Model of Data") are "relationships" (scare quotes, so informally used) between values of domains. Relations are not constrained to any numbers of "things they so connect". They typically correspond to the rectangles in an E/R model and typically end up as table definitions in the database definition.

Relation__SHIP__s, in the usual E/R sense, typically denote inclusion dependencies between relations as defined in the foregoing sense (they correspond to the lines between the rectangles). They typically end up corresponding to a FK constraint in the database definition.

There are similarities, because of course a value of a domain too has to exist ("be valid") before it can be "connected" in a relationship. This is often confusing newbees.

Whether to regard some relevant aspect of your business as being a domain or as a table is often obvious, but not always so. Think, e.g. countries. Is the pace at which they emerge and disappear fast enough for your actual business problem to have to take them into account as being something variable (table of all countries, can be altered as needed once in every ten years) or can you manage without (domain of, say, ISO codes).

Upvotes: 0

Related Questions