This discussion is archived
4 Replies Latest reply: Nov 29, 2011 3:31 PM by jschellSomeoneStoleMyAlias RSS

Design suggestion

mlvenkatesh Newbie
Currently Being Moderated
Requirement : Some of the department values say deptno., deptname, deptManager when amended(edited) is subjected to authorisation. Only after authorisation these department values gets persisted in database table - DEPARTMENT. The DepartmentBean is the java bean.

DEPARTMENT TABLE
DEPT_NO
DEPT_NAME
DEPT_MANAGER

For this requirement, an intermediary table has been designed that stores the values and where these values have to be persisted after authorisation.
The table structure is like this :-
INTERMEDIARYTABLE_
VALUE <<The amended/edited value>>
TABLE_NAME << DEPARTMENT>>
COLUMN_NAME <<DEPT_NO/DEPT_NAME/DEPT_MANAGER>>
STATUS <<APPROVED/PENDING>>

There is a properties file which maintains the mapping between javabean and table /column like deptNo = DEPT_NO
Once authorised the status will change to 'approved', the value will be stored against its TABLE_NAME/COLUMN_NAME.
P.S : I have taken department as an entity for example there are many other entities having the same requirement

QUESTION :

For me somehow the very idea of storing a column name (DEPARTMENT.DEPT_NO) inside a database table (INTERMEDIARY_TABLE) doesn't look right and good.
Any other alternative? Please suggest.
  • 1. Re: Design suggestion
    EJP Guru
    Currently Being Moderated
    I agree. I wouldn't store anything anywhere until authorized.
  • 2. Re: Design suggestion
    mlvenkatesh Newbie
    Currently Being Moderated
    It has to be stored for the authoriser to review and approve the edited value. Sorry if i had posted this in a wrong forum topic.
  • 3. Re: Design suggestion
    EJP Guru
    Currently Being Moderated
    In that case I would make a clone of each table concerned for authorization. Standard SQL doesn't support dynamic column names anyway so I fail to see the actual point of the original design.
  • 4. Re: Design suggestion
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    user13534543 wrote:
    Requirement : Some of the department values say deptno., deptname, deptManager when amended(edited) is subjected to authorisation. Only after authorisation these department values gets persisted in database table - DEPARTMENT. The DepartmentBean is the java bean.
    So you have a work flow.
    1. Record suggested change
    2. Review suggested change
    3. Post actual change.


    Once authorised the status will change to 'approved', the value will be stored against its TABLE_NAME/COLUMN_NAME.
    What happens if it isn't authorized?
    P.S : I have taken department as an entity for example there are many other entities having the same requirement
    Where many = 10, 100 or 1000?
    For me somehow the very idea of storing a column name (DEPARTMENT.DEPT_NO) inside a database table (INTERMEDIARY_TABLE) doesn't look right and good.
    It is ok but whether is it ideal depends on business needs.

    What are the real user situations where data needs to change?
    Is this something where there is one single update once a week?
    Or is there one column that gets updated every hour but everything else rarely gets updated?
    Or is that for any given record there are multiple updates per row at one time?

    And what is the review process? Is there a GUI? Are there multiple different people reviewing multiple different things?

    I will note that your current design lends itself to code generation in that you can populate allowed values via an constraint to an enumeration table by generating the values directly against a schema.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points