Make Inserts Perform Like Updates

[dgerundo]

I have a strange requirement for changing data in a table.



The current database design is a dimensional design (as opposed to a relational design). There is a Fact table called PERSON_FACT (name, address, Expertise_id, Industry_Id, Status_id, Country_id.....), EXPERTISE_DIM(Expertise_id, Expertise_Desc), INDUSTRY_Dim(Industry_id, Industry_Desc) and so forth. The 'dim' tables are basically 'lookup' tables and very common in this type of design.

Inserting rows into the PERSON table is straight forward. The dim tables have the valid values and the appropriate id's are inserted into the PERSON_FACT table. But there is a requirement that when an attribute in the PERSON_FACT table changes rather than perform an update a new record is inserted into the PERSON_FACT table so as to keep the history of what changed and when it was changed. I would like the user to be able to go to the specific 'person' and make the change but behind the 'scene' have the database perform the Insert rather than an update. This would require that all of the unchanged attributes be 'copied' to the new record.

Over time there will be cases where a person has several 'historical' records. In that case i believe it would be helpful for the user to see the history and the current record as well. And then be able to modify the current record.

I would welcome some suggestions as to a good design approach to satisfy this requirement.

thanks,
don

[ItsMe]

Is your question regarding creating the correct table structure and a diagram or is it that you have the diagram and now you are wondering how to build an application that manages the events?

[Perceptus]

If you had a dummy table with the same structure as the history table. Then you could make data entries into the dummy table using data entry mode. Then after the data is entered, use an event to run some queries. Run a query to copy to the history table, verify it was copied and delete from the dummy table.

[CJ_London]

basically you are talking about history tables. There are a number of ways this can be done - one as you suggest, another is to keep the current view in your main table, but copy the revised values to a separate table (i.e.update main table, insert to history table) and a third method is to do this by just updating an audit table with those fields that have changed.

Which is right for you depends on why you are doing it. Your suggestion is potentially ideal for example for tracking ownership of an account by a sales person where you are constantly reporting who owned what and when. The second method can also do this but involves maintaining two tables but simple to reference history when required, whilst the third method is really for reporting who changed what and when.

Your method is likely to become slower as the table size grows - you will need a subquery to determine the latest 'view' or the view at a particular point in time which can slow performance. The second method is slower in that you have an update and insert command to run for each change - ok for manual input but probably not for imported data - same goes for the third method, however if you are normally only interested in the latest view most of the time it will be faster to retrieve data than the first method.

I've used all three methods for different requirements. From what you describe it sound like method 2 is the most appropriate for you - you just need an insert command in the form before update event. Alternatively investigate data macros (I've not used them so can't help with that). If you want to stick with your suggestion just have the form unbound(i.e. no recordsource) and an 'update' button which actually does an insert.

[dgerundo]

My question is not about the table structures. It is about the user interface. I am assuming that we have the tables that I have outlined. I am more concerned with how to design the user interface. I want the user to search for a particular record in the PERSONS_Fact table. For example let us assume he has searched for 'John Smith' and the following data is returned:
firstName lastNe Address Status Expertise Industry
John Smith 5 High St Active Mechanic Automotive

Note that the first name and last name and address come from the PERSONS_Fact table but the other attributes come from the other dim tables that are joined to the PERSONS_Fact table.

Now our goal is to change one of the attributes values --- for example the STATUS, from Active to Inactive. But behind the 'scenes' we want the application to insert another row into the database with all of the unchanged original values and the changed Status value of Inactive. So now we should have 2 rows from John Smith in our database.

firstName lastNe Address Status Expertise Industry
John Smith 5 High St Inactive Mechanic Automotive

I believe there are several ways of creating the user interface to do this. I do not think it is straightforward since if we create a from that is bound to a table with various combo boxes if we change a value we merely update the value rather than perform an insert in the database table.

Here is one way of doing it but not very elegant:

OPTION I
1) Have the user choose the PERSON that he is interested in with a lookup. This record would then be displayed.
2) Then the user would click on a button to duplicate the PERSON's data and this would be displayed below the original data
3) Now the user would make a change to the attribute(s) that he is interested in

[orange]

You may find some guidelines here

[CJ_London]

It is about the user interface

suggest read my post again

....method 2 is the most appropriate for you - you just need an insert command in the form before update event....

....If you want to stick with your suggestion just have the form unbound(i.e. no recordsource) and an 'update' button which actually does an insert

How you populate the form by searching for a record is up to you - it was not what you asked

[ItsMe]

What I will do in cases like this is create a custom Class and use enums. The custom class can be instantiated to perform an action for different tables and or columns. One class might be dedicated for causing an item to become inactive and another class could be dedicated for creating a new item, etc. The enums would be used to pass the names of tables and columns to the class objects.

I have seen examples that use the table's structure and the ordinal position of the column. I prefer using enums and strongly typed code.