On modeling pairs with a possibly empty second element EDIT: I've made the post significantly shorter.  I hope it's more digestible now.



Consider the following schema:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE VIEW mydata AS

  SELECT X.x, Y.y

    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y);


This schema enforces the following constraints on the rows of the mydata view:


no two rows may have the same y-value but different x-values;
the x-value is never empty;
the y-value is never emtpy;


The following output listing would be consistent with this schema:

Listing 1

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b | j
 c | k
 d | l
 a | h


Now, suppose that instead of the three constraints above we had the following:

&nbsp;&nbsp;1'. no two rows may have the same non-empty y-value but different x-values;

&nbsp;&nbsp;2'. the x-value is never empty;

(Constraint 2' is the same as constraint 2; constraint 1' entails adding the qualifier "non-empty" to constraint 1.)

With these new constraints the following listing, where the missing y-values are NULL, would become valid:

Listing 2

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h


But such a listing would not be possible with the schema shown above.  Even if the NOT NULL constraint were removed from the definition of the _DATA.y column, the closest we would get is

Listing 3

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h


...because there's no way to determine the appropriate value of x for those rows where y is NULL.

I'm looking for a normalized schema that would enforce the new set of constraints (and thus allow data like that shown in Listing 2).



My "non-solution"

My "non-solution" to this problem is a hard-to-maintain hack.  I post it here for three reasons:


to clarify the situation described in the question above;
to provide table-initialization code that responders can use to test their proposals if they so wish (it's in fact the code I used to generate Listing 2);
to give an example of the sort of hard-to-maintain hack that I'm trying to avoid.


This "non-solution" consists of defining some distinct values of y that can somehow be recognized as NULL, and redefining mydata to make use of this information.  For example:

INSERT INTO X VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('NULL_b', 'b')
  , ('k', 'c')
  , ('NULL_d', 'd')
;

INSERT INTO _DATA VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'NULL_b')
  , (4, 'k')
  , (5, 'NULL_d')
  , (6, 'h')
;


CREATE TEMP VIEW mydata AS
  SELECT x,
         CASE WHEN y LIKE 'NULL_%'
              THEN NULL
              ELSE y
         END AS y
    FROM X JOIN     Y USING (x)
           JOIN _DATA USING (y);


Then

SELECT * FROM mydata;
 x | y 
---+---
 a | h
 b | i
 b | 
 c | k
 d | 
 a | h
(6 rows)


Ugly as sin, but aesthetic aside, it is also very hard to maintain.
Other options I see:

A) add column x to table _data. A few more changes will be required:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x),
    , UNIQUE (x, y)                    -- required for the modified FK below
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , y TEXT
    , FOREIGN KEY (x, y) REFERENCES Y(x, y)    -- modified FK
    , FOREIGN KEY (x) REFERENCES X(x)          -- additional FK
);


The view has to be modified as well:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA USING (x) 
  WHERE _DATA.y IS NULL ;


Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, x, y) VALUES
    (1, 'a', 'h')
  , (2, 'b', 'i')
  , (3, 'b', NULL)
  , (4, 'c', 'k')
  , (5, 'd', NULL)
  , (6, 'a', 'h')
;

SELECT x, y FROM mydata;
 x | y 
---+---
 a | h
 b | i
 c | k
 a | h
 b | 
 d | 
(6 rows)




B) separate the data that have no y value associated to another table. No changes at all required to the existing tables, only for the view:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE TABLE _DATA_X (
      data_x_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);


The view modified:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL 
    FROM X JOIN _DATA_X USING (x) ;


Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, y) VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'k')
  , (4, 'h')
;

INSERT INTO _DATA_X (data_x_id, x) VALUES
    (1, 'b')
  , (2, 'd')
;

SELECT x, y FROM mydata;
 x | y 
---+---
 a | h
 b | i
 c | k
 a | h
 b | 
 d | 
(6 rows)