null problem when building sql transaction associate table

Question

Here is the original table:

| country  | hostingcity |hostingembasy | statesecretary |
| -------- | ----------- | ------------ | -------------- |
| U.K.     | London      | U.S.A        | John           |
| U.K.     | London      | China        | Li             |
| U.K.     | London      | Japan        | Akira          |
| Germany  | Berlin      | U.S.A        | John           |
| Germany  | Berlin      | Austria      | Jurgen         |
| N.Korea  | null        | null         | Kim            |

A lot of redundancy and given hostingembasy and statesecretary don't depend on primary key country we can create two tables:

First table:

| country  | hostingcity |
| -------  | ----------- |
| U.K.     | London      |
| Germany  | Berlin      |
| N.Korea  | null        |

Second table:

| hostingembasy | statesecretary |
| ------------- | -------------- |
| U.S.A         | John           |
| China         | Li             |
| Japan         | Akira          |
| Austria       | Jurgen         |
| null          | Kim            |

And transaction table:

| country | hostingembasy |
| ------- | ------------- |
| U.K.    |  U.S.A        |
| U.K.    |  China        |
| U.K.    |  Japan        |
| Germany |  U.S.A        |
| Germany |  Austria      |
| N.Korea |  null         |

Of course, this can not work, because the primary key in the second table can not be null. So how to deal with this kind of problem when we want to try and normalize tables? This is just an example, but I can think of many cases like this. I guess it must be quiet often to see a problem like this.

(1) Are you trying to convert your initial table into 3NF? Can a country only have 1 hosting city? (2) Do country and hostingembassy have the same set of values and, if not, how do they differ? (3) Does hostingembassy and statesecretary have a 1:1 relationship? — NickW, Feb 01 '21 at 18:34
Imagine that source data contains one more row with NULLs in `hostingcity` / `hostingembasy` - how do you think, is it possible to distinguish these NULLs? Replace NULLs with some definite data. — Akina, Feb 01 '21 at 18:37
Does this answer your question? [What to do with null values when modeling and normalizing?](https://stackoverflow.com/questions/40733477/what-to-do-with-null-values-when-modeling-and-normalizing) — philipxy, Feb 04 '21 at 00:22
What won't work? Why do you think null is part of all CKs? (There may well be a problem, but you're not clear about what the problem is.) PS You write "create two tables", but how exactly are you dxomposing & how does the original get recreated? What if there are 2 nulls in a column of the original base? Do you think recomposition is by natural join, as with decompositions per normalization to higher NFs? PS Please clarify via edits, not comments. — philipxy, Feb 04 '21 at 00:30

score 0 · Answer 1 · answered Feb 01 '21 at 18:46

You seem to want three create table statements:

create table table1 as
    select distinct country, hostingcity
    from original;

create table table2 as
    select distinct hostingembasy statesecretary
    from original;

create table table3
    select distinct country, hostingembasy
    from original;

null problem when building sql transaction associate table

1 Answers1