1

I am working on a practice questions for ERD, and I was wondering what the correct approach is for modelling either or relationships.

For example, in a Taekwondo school, you will have customer accounts, which will represent and pay for one or many students. The account is owned by either a parent, or a the student himself. Therefore the account owner is either a parent or a student. What is the best way to represent a relationship like this?

Here is what I came up with, but I am unsure if this conforms to best practice:

enter image description here

user11073489
  • 117
  • 2
  • 8
  • 1
    Wouldn't the account_id make more sense in the person entity rather than student? Are you asking how to *draw* an exclusive subtype or how to create it in a database? There are various different notations that support exclusive subtypes https://medium.com/@ericgcc/dont-get-wrong-explained-guide-to-choosing-a-database-design-notation-for-erd-in-a-while-7747925a7531 – nvogel Dec 02 '19 at 19:45
  • An account can cover several students (ex a parent with several kids at the school), so the `account_id` in `students` table is there to keep track of who the account needs to pay for. The `account_owner` in `accounts` is the person who actually pays for the account. – user11073489 Dec 02 '19 at 20:06
  • This is exclusive subtyping/inheritance. Where are you stuck in the textbook/reference method you are following? Moreover, this is a faq. Please don't ask for yet another presentation. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. PS Clarify via edits, not comments. – philipxy Dec 03 '19 at 00:49
  • 1
    @nvogel. _Wouldn't the account_id make more sense in the person entity rather than student?_ No. 1) The field would then be NULLable. 2) Account is a child of Person, not the other way around. – PerformanceDBA Dec 08 '19 at 04:44

1 Answers1

2

1 Clarification

Representing an either-or relationship in Crows foot ERD

The diagram you have is a good start. Note:

  • that is not ERD. That is way more detail than an ERD can handle
  • ERD does not have a Crows Foot, that is IEEE notation

Ultimately, you need a data model that has the detail required for an implementation (way more than ERD). That is why I said your diagram is a good start, it is moving in that direction. However, we have a Standard for Relational Data Modelling: IDEF1X, the Standard for modelling Relational databases since 1993, available since 1984 before it was elevated to a standard.

  • Evidently both Dr E F Codd's Relational Model, and the diagrammatic method for modelling Relational databases is suppressed.

The relationship symbol, especially the cardinality, in IEEE notation is better (more easily understood) than IDEF1X, therefore most people use that. All data modelling tools, such as ERwin, implement IDEF1X, and allow either IDEF1X or IEEE notation for relationships.

2 Request

The diagram as intended is illegal. Why ? Because you have one relationship going "out" of Person, to two tables. Not possible. You are asking how to represent such a relationship in a data model (not possible in ERD). The answer is, that is an OR Gate is logical terms, a Subtype in Relational terms.

Please inspect these answers for overview and detail. Follow the links for implementation details and code:

Subtypes can be:

  • Exclusive (the Basetype must be one of the Subtypes), or

  • Non-Exclusive (the Basetype must be any [more than one] of the Subtypes).

From Role it appears to be Exclusive. What you call Role is a Discriminator in IDEF1X.

That is best practice for Relational databases.

Relational Data Model

This is best practice for for data models (this level of detail shows attribute name only).

Foo

  • Of course, all my data models are rendered in IDEF1X.

  • My IDEF1X Introduction is essential reading for beginners.

  • ParentId, StudentId, OwnerId are all RoleNames (Relational term)of PersonId. This makes the context of the FK explicit.

3 Correction

but I am unsure if this conforms to best practice

Since you are concerned, there is one other issue. There is a mistake in your model, it is one of the common errors that happen when one stamps id on every file. Such a practice cripples the modelling exercise, and makes it prone to various errors. (I understand that you are taught that crippling method.)

  • Since a Person can have 0-or-1 Account, and the Person PK (which is unique to a Person), is a FK in Account, it can be the PK in Account.

  • AccountId is not necessary: it is 100% redundant, one additional field and one additional index, that can be eliminated.

Foo2

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90