4 Replies Latest reply: Nov 29, 2011 5:31 PM by jschellSomeoneStoleMyAlias RSS

    Design suggestion

    mlvenkatesh
      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
          I agree. I wouldn't store anything anywhere until authorized.
          • 2. Re: Design suggestion
            mlvenkatesh
            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
              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
                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.