Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    4

    Generic Table Defs?

    Hi ...

    I am creating a design for an application which requires the following.

    There are multiple projects.
    For each project, the app collects data from multiple sources.
    For each project, the app performs calculations based on the input data and produces results.

    Each project will have different sets of inputs, hence different sets of outputs.

    For e.g.
    Project PRJ01
    Inputs
    a Total Revenue this week ($56,000)
    b Total Products sold this week (5673)
    c Total Unique Products sold (123)
    Sources
    SOU01
    SOU02
    Project PRJ02
    Inputs
    a Total number of Active Employees (234)
    b Total new Employees (13)
    c Total terminated Employees (4)
    Sources
    SOU01
    SOU02

    As you can see, the projects are never related to each other.
    But since the app should allow multiple projects to be created.
    I can not create sep table for each project since the projects will be created
    over time and this app should be able to handle this without creating new tables.

    This is what I thought ... I am sure that there has to be a better way out there.

    TABLE = SOURCE
    PK SOURCE_CODE (e.g. SOU01)
    SOURCE_DESC (e.g. Source Desc)

    TABLE = PROJECT
    PK PROJECT_CODE (e.g. PRJ01)
    PROJECT_DESC (e.g. Project Desc)

    I have a generic INPUT_TABLE with the following fields

    TABLE = INPUT_TABLE
    PK PROJECT_CODE
    PK SOURCE_CODE
    INPUT_1
    INPUT_2
    ...
    INPUT_40

    TABLE = INPUT_TABLE_MAPPING_BY_PROJECT
    PK PROJECT_CODE (PRJ01)
    PK INPUT_TABLE_GENERIC_COLUMN (INPUT_2)
    PROJECT_COLUMN_NAME (Total Products sold this week)

    I have a similar generic one for OUTPUT
    TABLE = OUTPUT_TABLE
    PK PROJECT_CODE
    PK SOURCE_CODE
    OUTPUT_1
    OUTPUT_2
    ...
    OUTPUT_40

    TABLE = OUTPUT_TABLE_MAPPING_BY_PROJECT
    PK PROJECT_CODE (PRJ01)
    PK OUTPUT_TABLE_GENERIC_COLUMN (OUTPUT_2)
    PROJECT_COLUMN_NAME (% of Products sold)

    For calculating the outputs, I have the following table.

    TABLE = OUTPUT_CALC
    PK PROJECT_CODE
    PK OUTPUT_1
    OUTPUT_SQL (SQL Expression)

    So for each output column, the program logic will go pick the SQL from here and execute it to get the value for the corresponding column.

    Any thoughts and comments will be highly appreciated.
    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I am "suspicious" of this requirement: if no 2 projects have anything in common, then how will the app know how to perform the calculations for each project's unique sets of data? Surely you'll need a new app for each project (or a new module in the app).

    This smacks of a lack of analysis to find out what the requirements really are and what they have in common, and/or an attempt to build the One Great Database that will somehow meet all possible current and future requirements for anything without modification. If that were possible we'd all have one!

  3. #3
    Join Date
    May 2008
    Posts
    4
    Hi Andrew,
    Thanks for your response.

    The projects are similar in the sense that they all have a set of input
    parameters and a set of output values.

    The genaralized input table is managed by
    TABLE = INPUT_TABLE_MAPPING_BY_PROJECT
    PK PROJECT_CODE (PRJ01)
    PK INPUT_TABLE_GENERIC_COLUMN (INPUT_2)
    PROJECT_COLUMN_NAME (Total Products sold this week)

    Similarly I have the OUTPUT_TABLE_MAPPING_BY_PROJECT for the output
    mapping.

    The relation between INPUT and OUTPUT is the calculations which need
    to be performed on INPUT values to derive the OUTPUT values.
    output c = input a - input b
    The calc will be stored as a SQL.

    TABLE = OUTPUT_CALC
    PK PROJECT_CODE
    PK OUTPUT_1
    OUTPUT_SQL (SQL Expression)

    I do realize that this is not the good solution ... but the project requires
    creation of new projects without code changes or creation on new dbs.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by popeye1974
    I do realize that this is not the good solution ... but the project requires
    creation of new projects without code changes or creation on new dbs.
    Well, all I can say then is "good luck with that"!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •