Hi Experts
I have an old data model designed like this
create table location_info_remove_me
(
id_info NUMBER,
location_desc varchar2(100),
constraint id_info_pk primary key (id_info)
)
insert into location_info_remove_me values (1, 'New York');
insert into location_info_remove_me values (2, 'Boston');
insert into location_info_remove_me values (3, 'Paris');
insert into location_info_remove_me values (4, 'Houston');
create table tbl_main_remove_me
(
key_id number(10) not null,
location_id_info number,
date_created DATE,
note_desc varchar(100),
CONSTRAINT key_id_pk PRIMARY KEY (key_id),
constraint location_id_fk foreign key(location_id_info) references location_info_remove_me(id_info)
)
insert into tbl_main_remove_me values (1, 1, to_date('12-13-2021', 'MM-DD-YYYY'), 'this is my info');
insert into tbl_main_remove_me values (2, 1, to_date('12-15-2024', 'MM-DD-YYYY'), 'my info is cool');
insert into tbl_main_remove_me values (3, 2, to_date('12-19-2025', 'MM-DD-YYYY'), 'where is my info');
It worked fine because user were allowed to pick only one location from the user interface, now the change is to allow the user pick multiple locations.
Potential solutions
Solution 1: create a bridging table with a primary key and update the tbl_main_remove_me table with the bridging table primary key as foreign key
Solution 2: going forward, just append multiple location_id in the tbl_main_remove_m. for example, 1, 2
Solution 1 is the best option but requires a huge data model change and effort and there is already a lot of data in the system
Solution 2 is easy to implement but doesn't conform to the best practices of normalization.
Hence, is there another solution worth looking at