APEX get Multiple entries FK

,i dont know what so search for my problem. I think its very simple, but i dont know about that 🙂

I have a Table where i listed all my “Groups/Teams”

SCA_TEAM
TEA_ID   PK
TEA_NAME
TEA_DATE_CREATED
TEA_CREATED_BY
TEA_DATE_MODIFIED
TEA_MODIFIED_BY

And then i have a Users Table where i list all the People:

SCA_USERS
USR_ID  PK
USR_NAME
USR_DEPARTMENT
USR_TEA_ID FK
USR_DATE_CREATED
USR_CREATED_BY
USR_DATE_MODIFIED
USR_MODIFIED_BY

I use APEX 18.1 and have a Form with a List of Values, there i can select one of the Groups i have added at the other Table…

So my question is, how i can assign more Groups to one User? Do i need to add more FK Columns? or can i store like multiple IDs 1;2;3; etc..

Best case would be a form where i can select one Group, then klick a “Plus” and select anotherone..

i hope you understand what i wanted to say

Thanks for your help

Best Regards Daniel

Answer

Apex offers Select List (as well as Shuttle) items that allow multiple selection. If you store such values into a database column, they will be stored as colon-separated values. For example, if you choose Roma, Juventus and Inter, they will be stored as Roma:Juventus:Inter (actually, their return values, which would then be some IDs, such as 23:8846:2231). No problem with that, you have what you wanted.

However, such a principle reads good night, normalization as you can’t do much with that. If you ever wanted to display values hidden behind such a list of stored IDs, you’ll have to split that string into rows and join it with the master table. That’s not impossible, of course, but requires some coding. For example:

SQL> select * From sca_team;

    TEA_ID TEA_NAME
---------- --------------------
         1 Roma
         2 Juventus
         3 Inter

SQL> select * from sca_users;

    USR_ID TEAMS
---------- --------------------
       103 2:3:1

SQL> select t.tea_name
  2  from sca_team t join
  3    (select regexp_substr(u.teams, '[^:]+', 1, level) team_id
  4     from sca_users u
  5     where u.usr_id = 103
  6     connect by level <= regexp_count(u.teams, ':') + 1
  7    ) x on x.team_id = t.tea_id
  8  order by t.tea_name;

TEA_NAME
--------------------
Inter
Juventus
Roma

SQL>

What’s worse is that – if anyone manually edits such a column value, they can enter invalid IDs that don’t exist in the master table. In other words, no referential integrity is maintained.

Therefore, although you can do that, I don’t suggest to do it.

Usual way of handling such situations is to create a new table which contains pairs of values, e.g.

create table user_x_team
  (usr_id    number    constraint fk_uxt_user references sca_users (usr_id),
   tea_id    number    constraint fk_uxt_team references sca_team  (tea_id),
   --
   constraint pk_uxt primary key (usr_id, tea_id)
  );

• foreign key constraints will make sure that you can’t enter invalid values for users nor teams
• primary key constraint makes sure that you can’t have duplicates

In Apex, you could choose an Interactive Grid to handle such a situation, as it allows you to enter as many rows as you want (simply by pressing the “Add row” button).