I know this is overall a database design question, but I think there is validity of listing it with APEX due to the nature of the implementation I am thinking of with use of APEX_STRING.SPLIT_NUMBERS api.

With that out of the way, I have a consent form table that once created will need to be assigned to different ‘target’ groups (e.g. School Pyramid, School Level [High School, Middle School, Elementary], Grade Level [1-12], or even specific schools [by a School ID]). More than 1 pyramid can be targeted (total of 5), more than 1 school can be targeted (up tp 10) for a given consent form.

My initial thought was a table that has all these target groups (with associated reference/lookup tables REF_PYRAMID, REF_SCHOOL_LEVEL, REF_SCHOOL). Then store the IDs in the associated column as a list of numbers (eg. 2:12:13). Then I would use APEX_STRING.SPLIT_NUMBERS & MEMBER OF when I need to run SQL check to see if parent is part of an authorized target & should have access to the consent form.

I am not used to storing info this way so wondering if there are any downsides of this approach? This keeps the record flat and allows the option to make a consent form school-wide, which is just Y/N option not multiple targets, like the others.

dpc_consent_form_target {
  target_id                 number         [primary key]
  consent_form_id           number         [ref: > dpc_consent_form.consent_form_id]
  school_wide               char(1)        [chk 'Y','N']
  pyramid_id                number
  school_id                 number
  school_level_id           number
  grade_id                  number
}

My second thought was to normalize the design a bit more so that each, say School, would have it’s own record in a TARGET table, but then I’m not sure how best to handle the single "record" of school-wide.

table dpc_consent_form_target {
  target_id                 number         [primary key]
  consent_form_id           number         [ref: > dpc_consent_form.consent_form_id]
  target_type_id            number         [ref: > dpc_ref_target_type.target_type_id]
  target_value_id           number
  created_by                varchar2(50)
  created_date              timestamp
  updated_by                varchar2(50)
  updated_date              timestamp
}

table dpc_ref_target_type {
  target_type_id            number         [primary key]
  target_type_name          varchar2(50)
  active_flag               char(1)
  created_by                varchar2(50)
  created_date              timestamp
  updated_by                varchar2(50)
  updated_date              timestamp
  deleted_by                varchar2(50)
  deleted_date              timestamp
}

TARGET TYPE VALUES:
1 - pyramid_id
2 - school_level_id
3 - school_id
4 - grade_id
5 - school_wide

Thanks in advance for any advice.

>Solution :

Then store the IDs in the associated column as a list of numbers (eg. 2:12:13).

Don’t do that.

Apex concatenates RETURN values for e.g. Shuttle items and everything works just fine in Apex world. However, as soon as you step out of it and have to write queries based on such a data, you’ll have to split values into rows so that you could join them to other table(s).

It works, can be done, no problem; however, that’s not the way to do it because data model isn’t normalized. Also, you can’t create any meaningful index on it because you’d be indexing a colon-separated string, not each value within; no difference in performance when you have a small data set, but it will suffer on large data sets.

What to do? Create a child table which will reference its master table via foreign key constraint.