locked
Table Schema RRS feed

  • Question

  • Hi,

    I want to store Stock Standard Cost every month for each year which schema suits best as per performance wise.

    please advice.

    Schema 1

    CREATE TABLE [dbo].[StockItemStandardCost](
    [ID] [int] NOT NULL,
    [StockID] [smallint] NULL,
    [StdYear] [smallint] NULL,
    [StdMonth] [tinyint] NULL,
    [StdCost] [smallmoney] NULL,
     CONSTRAINT [PK_StockItemStandardCost] PRIMARY KEY CLUSTERED 

    Schema 2

    CREATE TABLE [dbo].[StockItemStandardCost](
    [ID] [int] NOT NULL,
    [StockID] [smallint] NULL,
    [StdYear] [smallint] NULL,
    [StdCostJan] [smallmoney] NULL,
    [StdCostFeb] [smallmoney] NULL,
    [StdCostMar] [smallmoney] NULL,
    [StdCostApr] [smallmoney] NULL,
    [StdCostMay] [smallmoney] NULL,
    [StdCostJun] [smallmoney] NULL,
    [StdCostJul] [smallmoney] NULL,
    [StdCostAug] [smallmoney] NULL,
    [StdCostSep] [smallmoney] NULL,
    [StdCostOct] [smallmoney] NULL,
    [StdCostNov] [smallmoney] NULL,
    [StdCostDec] [smallmoney] NULL,
     CONSTRAINT [PK_StockItemStandardCost] PRIMARY KEY CLUSTERED 

    Thank you.

    Thursday, March 20, 2014 3:57 AM

Answers

  • Using schema 1, you will have definite predicate with Year and month along with stock ID.

    You might have many NULL values for other months(say you are in Jan now) and you may need to update your rows every month to accommodate the Stock Cost.

    To me, schema1 is looking more normalized approach than schema2.

    • Marked as answer by tracycai Monday, March 31, 2014 5:48 AM
    Thursday, March 20, 2014 4:59 AM
    Answerer
  • Yeah, agreed, schema 1 is way more normalized (each row represents 1 standard cost at a given time and for a given product), and should be far easier to use. Instead of having to do a CASE WHEN MONTH = JANUARY THEN StdCostJan WHEN.. you simply have to use a where clause to determine the time period and always use the same column.]

    I might suggest a small change as well

    CREATE TABLE [dbo].[StockItemStandardCost](
    [ID] [int] NOT NULL,
    [StockID] [smallint] NULL,
    [StdYear] [smallint] NULL,
    --[StdMonth] [tinyint] NULL,
    StartTime  datetime2(7), --may change this to a different type, but this would be safest...
    EndTime  datetime2(7),
    StdMonth as (MONTH(StartDate)) PERSISTED,
    [StdCost] [smallmoney] NULL,
     CONSTRAINT [PK_StockItemStandardCost] PRIMARY KEY CLUSTERED 

    Now you have two ways to fetch the row, either by using a time value and a between (I would see startTime like 1/1/2014 and endtime as 1/31/2014 23:59:59.9999999 so using sysdatetime() or any date value you can get the current pricing with just a predicate like

    WHERE TimeValue between StockItemStandardCost.StartTime and StockItemStandardCost.EndTime

    Or you can also (assuming you may have the month and year value calculated already from some location) do:

    WHERE YearValue = StockItemStandardCost.StdYear
    and  MonthValue = StockItemStandardCost.StdMonth


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Friday, March 21, 2014 5:18 AM

All replies

  • I would definitely prefer SCHEMA1.

    Apart, I would also prefer StockID,Year,Month combined together can be your primary key.

    Thursday, March 20, 2014 4:04 AM
    Answerer
  • Hi,

    Thank you Latheesh for your valuable support.

    Can you please explain why you recommend Schema 1.

    Thank you.

    Thursday, March 20, 2014 4:11 AM
  • Using schema 1, you will have definite predicate with Year and month along with stock ID.

    You might have many NULL values for other months(say you are in Jan now) and you may need to update your rows every month to accommodate the Stock Cost.

    To me, schema1 is looking more normalized approach than schema2.

    • Marked as answer by tracycai Monday, March 31, 2014 5:48 AM
    Thursday, March 20, 2014 4:59 AM
    Answerer
  • Yeah, agreed, schema 1 is way more normalized (each row represents 1 standard cost at a given time and for a given product), and should be far easier to use. Instead of having to do a CASE WHEN MONTH = JANUARY THEN StdCostJan WHEN.. you simply have to use a where clause to determine the time period and always use the same column.]

    I might suggest a small change as well

    CREATE TABLE [dbo].[StockItemStandardCost](
    [ID] [int] NOT NULL,
    [StockID] [smallint] NULL,
    [StdYear] [smallint] NULL,
    --[StdMonth] [tinyint] NULL,
    StartTime  datetime2(7), --may change this to a different type, but this would be safest...
    EndTime  datetime2(7),
    StdMonth as (MONTH(StartDate)) PERSISTED,
    [StdCost] [smallmoney] NULL,
     CONSTRAINT [PK_StockItemStandardCost] PRIMARY KEY CLUSTERED 

    Now you have two ways to fetch the row, either by using a time value and a between (I would see startTime like 1/1/2014 and endtime as 1/31/2014 23:59:59.9999999 so using sysdatetime() or any date value you can get the current pricing with just a predicate like

    WHERE TimeValue between StockItemStandardCost.StartTime and StockItemStandardCost.EndTime

    Or you can also (assuming you may have the month and year value calculated already from some location) do:

    WHERE YearValue = StockItemStandardCost.StdYear
    and  MonthValue = StockItemStandardCost.StdMonth


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Friday, March 21, 2014 5:18 AM
  • It is not sufficient to have the surrogate PRIMARY KEY as unique only. Here is my guess for the natural key:

    CREATE TABLE [dbo].[StockItemStandardCost](
    [ID] [int] PRIMARY KEY,
    [StockID] [smallint] NULL,
    [StdYear] [smallint] NULL,
    [StdMonth] [tinyint] NULL,
    [StdCost] [smallmoney] NULL,
    UNIQUE (StockID, StdYear, StdMonth) 
    );
    


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, March 21, 2014 7:22 AM
  • does stockitem cost remain same over year and Month? I would replace it with dates instead as we cant predict when stiockitem cost can change. I think Louis approach of having two datetime fields would suffice. Year,Month etc if required can be derived from them.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 21, 2014 7:26 AM