Database design help required

database-design

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?

Best Answer

This doesn't call for any SCD design -- the state of an entity is not changing, slowly or otherwise. What you have is a series of events that you want to refer to the user associated with the event.

create table Executions(
    ID      int not null auto_increment,
    UserID  int not null,
    ExcDate date not null,
    <any other data concerning the Execution event>,
    constraint PK_Executions primary key( ID ),
    constraint FK_Execution_User foreign key( UserID )
        references Users( ID ),
);

Then remove any Execution related data (ExecutionID) from the Users table. Each execution event relates back to the associated user. So the Users table contains only user data and the Executions table contains only execution event data, including the user that performed the execution. And it maintains all such events not just the first and/or last.

Related Question