0

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?

  • 1
    What are all those names? And where do the first and last execution IDs come from? You want probably describe your real world problem in a bit more detail. – dezso Aug 31 '15 at 8:03
  • Basically, we have an execution process of validating a user, that is being done in SQL, and result is being saved in 50 odd tables, above described tables are like one of the 50 odd tables. User normally goes and triggers an execution and each execution has an internal ID which is executionID, when execution completes we save the result as described in approach one, but as a part of revamping the code, I am thinking of changing current storage structure. – Jack Aug 31 '15 at 9:19
  • The design will follow from the function. What do you what to do with this information? What is wrong with your current arrangement that you want to change it? What, specifically, is the problem you are trying to solve? – Michael Green Aug 31 '15 at 10:52
  • Problems: #1 In First approach - Repetition of data when same execution executes concurrently but one positive is each execution data is isolated. #2 In second approach - If 2 executions are executing simultaneously i.e. "a" & "b" and if "b" finishes its execution before hand then identification of first and last execution is tedious. #3 In third approach there is multiples of mapping tables, i have 50 tables if i keep mapping tables then count will go to 100 tables with mapping table. – Jack Aug 31 '15 at 11:05
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.