generic table used for many applications

need for a generic table.

Some times we might have to create a table which can be used by different applications. One of the approach is to create a table with generic columns and then create different views for each application.

structure of the generic table would be something like

create table generic_table(
                             char_field1 varchar2(100),
                             char_field2 varchar2(100),
                              char_field3 varchar2(100),
                             .............................
                              number_field1 number,
                              number_field2 number,
                              .....
                             )

now create a view for each each application.

say we need to insert emp data into this table

create view emp_view as
  select  number_field1 as empno,
            number_field2 as salary,
            number_field3 as deptno,
          char_field1 as first_name,
            .....


for the department  data

create view dept_view as
           select number_field1 as deptno,
                     char_field1 as dname,
                   .................

Use the above views for select, insert,update and delete operations from the underlying table

This kind of architecture is useful mainly when most of of the applications use almost same number of columns

You can extend the structure based on your requirements

holiday table

Today am writing about the holiday table needed in the organization

This is useful when we have to find the next business date/previous business date  before/after any given business date

create table public_holiday (holidays date)

insert only the holiday dates in this table.

if we need to find the previous business date

fn_nextbusinessdate(v_date)
loop
     check if the day is monday
         then day =day -3
     else day =day -1

  check if the day is holiday in the holiday table then repeat the above process
else exit the loop
loop


now extend the table and function based on our imagination

new test share topic