Search code examples
data-modelingoracle-apex-5

Oracle APEX - Data Modeling & Primary Keys

I'm creating a rather large APEX application which allows managers to go in and record statistics for associates in the company. Currently we have a database in oracle with data from AD which hold all the associates information. Name, Manager, Employee ID, etc.

Now I'm responsible for creating and modeling a table that will house all their stats for each employee. The table I have created has over 90+ columns in it. Some contain data such as:

  • Documents Processed
  • Calls Received
  • Amount of Doc 1 Processed
  • Amount of Doc 2 Processed

and the list goes on for well over 90 attributes. So here is my question:

  • When creating this table in my application with so many different columns how would I go about choosing a primary key that's appropriate? Should I link it to our employee table using the employees identification which is unique (each have a associate number)?

  • Secondly, how can I create these tables (and possibly form) to allow me to associate the statistic I am entering for an individual to the actual individual?

I have ordered two books from amazon on data modeling since I am new to APEX and DBA design. Not a fresh chicken, but new enough to need some guidance. An additional problem I am running into is that each form can have only 60 fields to it. So I had thought about creating tables for different functions out of my 90+ I have.

Thanks

Solution

  • 4.2 allows for 200 items per page.

    oracle apex component limits

    A couple of questions come to mind:

    1. Are you sure that the employee Ids are not recyclable? If these ids are unique and not recycled.. you've found yourself a good primary key.
    2. What do you plan on doing when you decide to add a new metric? Seems like you might have to add a new column to your rather large and likely not normalized table.

    I'd recommend a vertical table for your metrics.. you can use oracle's pivot function to make your data appear more like a horizontal table.

    If you went this route you would store your employee Id in one column, your metric key in another, and value...

    I'd recommend that you create a metric table consisting of a primary key, a metric label, an active indicator, creation timestamp, creation user id, modified timestamp, modified user id.

    This metric table will allow you to add new metrics, change the name of the metric, deactivate a metric, and determine who changed what and when.

    This would be a much more flexible approach in my opinion. You may also want to think about audit logs.