MySQL Normalization when column depends on 2 columns, of which exactly one is null

Question

In the Person table, either the Citizen_IdentityID or the Foreigner_WorkPermitID will be null.

The PersonID_ID holds either the value of the Citizen_IdentityID or Foreigner_WorkPermitID (whichever that is not null).

In other words, PersonID_ID is dependent on the value of either Citizen_IdentityID or Foreigner_WorkPermitID.

How should I structure the design for normalization?

The above entities are just a simple substitution of the actual use case.

This is an antipattern for database subtyping. Have you googled your title? Re "normalization", what is your reference? What does it say about normalizing NULLs? Have you googled normalization when there are NULLs? How would you normalize if NULL was just another value? Why haven't you mentioned FDs? Do FKs have anything to do with normalization? PS Your title is not your question. Please google many clear, precise specific phrasings of your actual question plus tags. If you still need to ask use one of those phrasings as a title. (How else ought you research your question or title it?) — philipxy, Jul 03 '17 at 06:19
Google your title & my 1st sentence. Please address my previous comment. Editing your title is a tiny part of it. And it comes after doing a bunch of other stuff. — philipxy, Jul 03 '17 at 06:49
PS I just googled 'database how to normalize with nulls' and my *third hit* is SO [What to do with null values when modeling and normalizing?](https://stackoverflow.com/q/40733477/3404097) which has an answer [*by me*](https://stackoverflow.com/questions/40733477/what-to-do-with-null-values-when-modeling-and-normalizing). (But I doubt you really mean to ask anything about normalizing here.) — philipxy, Jul 03 '17 at 07:25

score 0 · Answer 1 · answered Jul 03 '17 at 04:10

0

You can just create one table person,

Having following columns

id int auto increment
citizen_id int
workpermit_id int
name varchar

So that whenever there is need to use one of id between citizen_id or workpermit_id you can simply check it's absence by suing isnull()

answered Jul 03 '17 at 04:10

Chintan7027

But i have other attributes that depends only on `ID`, eg. `IDColor`. How would i normalize that? – James Jul 03 '17 at 04:43

1 Answers1