locked
one table or two tables for web app database involving stored procedure RRS feed

  • Question

  • Looking for some advice on the following situation -

    I am designing a SQL Server 2008R2 database for a web application.  The basic idea is users will enter numeric data, then click a button which causes a stored procedure to run calculations which generate output data.  The relationship of the output data to the input data is one to one.  The question is, should the output data be inserted into the same table that holds the user input data or should it go into a separate table?  It should be assumed that several users will be using the application simultaneously.  A couple of primary concerns I have about which way to go here include performance and security, though there may be other considerations people can identify.

    Sunday, August 5, 2012 6:29 PM

Answers

  • There is no specific need to store the input and output data separately for this project.  Yes, UserID will be a column in the table and the stored procedure will look for rows of input data that have no output data yet for that user and generate the output data.

    Based in the information provided, I suggest a single row containing both the input and output.  Specify NULL for the output values during the initial insert.  For example:

    CREATE TABLE dbo.Calculation(
    	UserID int NOT NULL
    		CONSTRAINT FK_Calculation_User FOREIGN KEY REFERENCES dbo.ApplicationUser(UserID)
    	,CalculationID int NOT NULL
    	,InputValue1 int NOT NULL
    	,InputValue2 int NOT NULL
    	,OutputValue1 int NULL
    	,OutputValue2 int NULL
    	,CalculationNeeded bit NOT NULL
    		CONSTRAINT DF_Calculation_CalculationNeededFlag DEFAULT (1)
    	,CONSTRAINT PK_Calculation PRIMARY KEY CLUSTERED (UserID, CalculationID)
    	);
    CREATE INDEX idx_Calcuation_1 ON dbo.Calculation(UserID, CalculationNeeded);


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, August 7, 2012 12:32 PM
    Answerer

All replies

  • Is there a specific need to store input and output data in separate rows?  A one-to-one relationship can be implemented as a singe row containing both input and output values.  I think that would provide the best performance since fewer rows are involved.

    I don't believe there's any difference from a security perspective between one or two tables.  Is userid passed as stored procedure parameter and included in each row to logically partition data by userid?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, August 5, 2012 6:49 PM
    Answerer
  • There is no specific need to store the input and output data separately for this project.  Yes, UserID will be a column in the table and the stored procedure will look for rows of input data that have no output data yet for that user and generate the output data.
    Monday, August 6, 2012 2:34 AM
  • I do not think you need to store an output data as it is calculated values in the tables at all... But perhaps for history reports...

    Do you really need to store calculated values (output data)?


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, August 6, 2012 5:04 AM
  • The calculations to generate the output data are quite complex which is why I think it's necessary to store them.
    Tuesday, August 7, 2012 12:01 AM
  • Still, if someone change the data  you will need to calculate again and update the old value.....

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, August 7, 2012 5:18 AM
  • There is no specific need to store the input and output data separately for this project.  Yes, UserID will be a column in the table and the stored procedure will look for rows of input data that have no output data yet for that user and generate the output data.

    Based in the information provided, I suggest a single row containing both the input and output.  Specify NULL for the output values during the initial insert.  For example:

    CREATE TABLE dbo.Calculation(
    	UserID int NOT NULL
    		CONSTRAINT FK_Calculation_User FOREIGN KEY REFERENCES dbo.ApplicationUser(UserID)
    	,CalculationID int NOT NULL
    	,InputValue1 int NOT NULL
    	,InputValue2 int NOT NULL
    	,OutputValue1 int NULL
    	,OutputValue2 int NULL
    	,CalculationNeeded bit NOT NULL
    		CONSTRAINT DF_Calculation_CalculationNeededFlag DEFAULT (1)
    	,CONSTRAINT PK_Calculation PRIMARY KEY CLUSTERED (UserID, CalculationID)
    	);
    CREATE INDEX idx_Calcuation_1 ON dbo.Calculation(UserID, CalculationNeeded);


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, August 7, 2012 12:32 PM
    Answerer