CODEWITHSUNDEEP
sqlpostgresql
Kashif Iqbal
Kashif Iqbal

Reputation: 153

SQL : table relationship depends on column value

I am novice at SQL using Postgres, and have been looking for this answer for a few days.

I have a table called identification

| id | name | type |

A type is a driving licence, passport, national id card etc.

If the type is a driving licence, the relationship should be with the driving licence table

| id | first_name | last_name | driving_licence_number |

If the type is passport, the relationship should be with the passport_table.

The way I can describe it in pseudo code is

if (type == 0) join with driving licence table

if (type == 1) join with passport table etc.

What would be the best way to model this, so I can easily query the identification table, and retrieve the correct info?

Upvotes: 0

Views: 678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't understand your question about "modeling". You seem to already have a data structure. You can query these tables using logic such as:

select . . .
from identifications i left join
     driving_licenses dl
     on i.type = 0 and
        i.name = dl.driving_licence_number left join
     passports p
     on i.type = 1 and
        i.name = p.passport;

This is definitely a reasonable data model. However, Postgres supports inheritance, which might be appropriate for this use-case.

Upvotes: 2

Related Questions