By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table jobs (
id integer primary key,
name varchar(255) not null
);
create table job_data (
job_id integer not null,
foreign key(job_id) references jobs(id),
field varchar(255) not null,
value_user varchar(255),
value_xml varchar(255)
);
insert into jobs values (1, 'Joe'), (2, 'Jane'), (3, 'Xiny');
insert into job_data values
(1, 'city', 'Berlin',null),
(1, 'phone', '1234567',null),
(1, 'type', 'fulltime',null),
(2, 'city', 'New York',''),
(2, 'phone', '33333333',''),
(2, 'type', 'parttime',''),
(3, 'city', null, 'Berlin'),
(3, 'phone', '', '123'),
(3, 'type', '', 'fulltime')
select * from job_data;
| job_id | field | value_user | value_xml |
|---|---|---|---|
| 1 | city | Berlin | null |
| 1 | phone | 1234567 | null |
| 1 | type | fulltime | null |
| 2 | city | New York | |
| 2 | phone | 33333333 | |
| 2 | type | parttime | |
| 3 | city | null | Berlin |
| 3 | phone | 123 | |
| 3 | type | fulltime |
alter table job_data add value varchar(255), add source varchar(255);
update job_data
set value = value_user, source = 'user'
where coalesce(value_user, '') <> '';
update job_data
set value = value_xml, source = 'xml'
where coalesce(value_xml, '') <> '';
alter table job_data drop value_xml, drop value_user;
alter table job_data modify value varchar(255) not null, modify source varchar(255) not null;
-- If a job can have multiple sources for a field
create unique index job_field_source_uniq on job_data(job_id, field, source);
select * from job_data;
| job_id | field | value | source |
|---|---|---|---|
| 1 | city | Berlin | user |
| 1 | phone | 1234567 | user |
| 1 | type | fulltime | user |
| 2 | city | New York | user |
| 2 | phone | 33333333 | user |
| 2 | type | parttime | user |
| 3 | city | Berlin | xml |
| 3 | phone | 123 | xml |
| 3 | type | fulltime | xml |
create view old_job_data as
select
job_id,
field,
case source when 'user' then value end as value_user,
case source when 'xml' then value end as value_xml
from job_data;
select * from old_job_data;
| job_id | field | value_user | value_xml |
|---|---|---|---|
| 1 | city | Berlin | null |
| 1 | phone | 1234567 | null |
| 1 | type | fulltime | null |
| 2 | city | New York | null |
| 2 | phone | 33333333 | null |
| 2 | type | parttime | null |
| 3 | city | null | Berlin |
| 3 | phone | null | 123 |
| 3 | type | null | fulltime |
select *
from jobs
where id in (
select distinct j1.job_id
from job_data j1
inner join job_data j2 on j1.job_id = j2.job_id
where (j1.field = 'city' and j1.value like '%York%')
and (j2.field = 'type' and j2.value like '%time%')
)
| id | name |
|---|---|
| 2 | Jane |