Need help designing in my database tables.
I have a set of table which are being populated on every execution, like wise this executions are user specific if user executes an execution data at that point of time data is being populated in table set.
I have 3 approaches to define data for my executions, but i am a bit confused which one to use. It would be great if someone throw some light or some better approach.
--APPROACH 1 CREATE TABLE #USERS( fname nvarchar(100), mname nvarchar(100), lname nvarchar(100), ExecutionID int )
In approach 1 data gets repeated for each execution, with its executionID.
--APPROACH 2 CREATE TABLE #USERS_2( fname nvarchar(100), mname nvarchar(100), lname nvarchar(100), FirstExecutionID int, LastExecutionID int )
In approach 2 data gets populated with first and last execution id, if same data gets repeated in consecutive execution, I won’t create new record instead update LastexecutionID to latest one. This is similar to SCD Type – 2.
--APPROACH 3 CREATE TABLE #USERS_3( fname nvarchar(100), mname nvarchar(100), lname nvarchar(100), mappingID int ) CREATE TABLE #USERS_Mapping_3( ExecutionID int, mappingID int )
Approach 3 is similar to first approach but I will be storing execution ID with its mapping ID in different tables.
Any other alternative for saving this sought of execution specific data?