Table structure for "tagging" field values

I have to set up a database where there is the requirement to "tag" field instances with at least a data entry source and timestamp (and probably some other data). I terms of a typical relational table structure, I have to tag data at the field level, not the record level.

For example, I have an EMPLOYEE record with many attributes: EMPLOYEE_ID, NAME, ADDRESS, etc.. EMPLOYEE_ID is a unique identifier for an employee but there may be different values for NAME, ADDRESS, etc.. I must "tag" each of field values.

So, after thinking about this for a short time, there are several approaches:

                    EMPLOYEE table
                       EMPLOYEE_ID:    111
                       NAME:           John Doe
                       NAME_SOURCE:    TSS
                       NAME_TIME_STAMP:1/1/95
                       ADDRESS:        123 St.
                       ADDRESS_SOURCE:  ...

I don't like this approach for the obvious example of having multiple NAME field values but only one ADDRESS value.

Another approach:

                   EMPLOYEE table
                      EMPLOYEE_ID:     111
                      FIELD_NAME:      NAME
                      FIELD_VALUE:     JJ
                      FIELD_SOURCE:    TSS
                      FIELD_TIME_STAMP:1/1/95

I don't like this approach because I will have both numeric and character types of data stored in the same field. Queries on specific FIELD_NAMEs could be slow when this table contains many, many records.

Don't let the simplicity of my example fool you, I will have many different different fields, many records, some fields with many sources but others with just one. I believe I need a generic solution similiar to the second approach. Any suggestions? Is there any smart Oracle database dictionary table(s) I can utilize?

Thanks, TSS. Received on Tue Mar 28 1995 - 22:35:00 CEST