When to create a subtype or simply use optional attributes

I’ve been practicing with Oracle SQL Developer for some time using school assignments and encountered some kind of dilemma while doing the Entity Relationship Diagram for one of those, here’s my issue:

On the assignment they mention that there’s a CLIENT entity with certain attributes, and if this CLIENT is married, they’ll ask for the partners_name, if not married, they’ll ask for this person’s date_of_birth. How would you approach this situation? Because I’ve been told that “there’s no right answer”, but I imagine that there must be a standard, or more popular way of solving these type of situations, and I imagined two options, which I’ll show of course.

OPTION 1: So, I thought that maybe I can add both attributes to the CLIENT entity, and make them optional and having something like this:

My personal issue with this is that I’d be creating a situation in which both can be null, when they tell me explicitly that if CLIENT is married then assign partners_name, if not date_of_birth, it sounds mandatory for one or the other. I mention this option despite of what I just said, because I don’t know if they actually do it anyways out there.

OPTION 2: Creating subtypes, one for a CLIENT that is single and another one for those who are not.

This does address the previous issue with date_of_birth and partners_name now being mandatory, but by doing this I’d be creating two more tables? (I haven’t explored subtypes outside of Data Modeler, so I’m not sure of how this will translate into SQL Developer).

---

When I did this assignment I went with Option 1, but to this day I keep wondering if Option 2 would’ve been better, I do think that, but my concern is… Is it worth for this case to have two extra entities such as MARRIED and SINGLE? Which could mean two extra tables, considering that they only have one attribute. Is there another option that I don’t see or know about? Maybe, I’d really like to hear opinions from people that know more about this and have seen way more cases than me.

NOTE: I did a bit of research as well, like trying to find PROS for the second option, this is what Oracle has to say about supertypes and subtypes at least. Also found this one, it’s a bit similar I think, but not exactly, so I’d rather make my own question.

Thank you for your time everyone.

Answer

Is it worth for this case to have two extra entities such as MARRIED and SINGLE? Which could mean two extra tables

They would not have any extra tables. For example, given your objects:

CREATE TYPE client IS OBJECT(
  id         INT,
  first_name VARCHAR2(200),
  last_name  VARCHAR2(200)
) NOT INSTANTIABLE NOT FINAL;

CREATE TYPE single UNDER client (
  date_of_birth DATE
) INSTANTIABLE FINAL;

CREATE TYPE married UNDER client (
  partner_name VARCHAR2(400)
) INSTANTIABLE FINAL;

Then you would have only one table:

CREATE TABLE clients OF client(
  id CONSTRAINT clients__id__pk PRIMARY KEY
);

Which could store both sub-types:

INSERT INTO clients VALUES ( SINGLE( 1, 'Alice', 'Adams', DATE '1900-01-01' ) );
INSERT INTO clients VALUES ( MARRIED( 2, 'Betty', 'Baron', 'Bob' ) );

Then:

SELECT c.*,
       TREAT(VALUE(c) AS single).date_of_birth AS date_of_birth,
       TREAT(VALUE(c) AS married).partner_name AS partner_name
FROM   clients c

Outputs:

ID	FIRST_NAME	LAST_NAME	DATE_OF_BIRTH	PARTNER_NAME
1	Alice	Adams	1900-01-01 00:00:00
2	Betty	Baron		Bob

db<>fiddle here