[popeye1974]

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!

[andrewst]

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!

[popeye1974]

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.

[andrewst]

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"!