Schema Design - Attributes Arrangement

  • Dear Experts,

    Please refer to the below schema for your kind perusal. I would like to know whether the colour highlighted attributes can be exist in the same table? because certain category items (Raw Material) doesn't have these characteristics.

    CREATE TABLE [RD].[StockItem](
    [StockItemID] [int] IDENTITY(1,1) NOT NULL,
    [StockCode] [varchar](50) NOT NULL,
    [Description1] [varchar](255) NOT NULL,
    [Description2] [varchar](255) NULL,
    [Description3] [varchar](255) NULL,
    [UnitMeasureID] [smallint] NOT NULL,
    [StockCategoryID] [smallint] NOT NULL,
    [StockSubCategoryID] [smallint] NOT NULL,
    [StockClassID] [smallint] NOT NULL,
    [ActualWeight] [smallmoney] NULL,
    [RecipeFormulationID] [smallint] NULL,
    [PostCuringTemperature] [smallint] NULL,
    [PostCuringTime] [smallint] NULL,

    [InternalNotes] [varchar](1000) NULL,
    [CastingWeight] [smallmoney] NULL,
    [InsertWeight] [smallmoney] NULL,
    [EpoxyWeight] [smallmoney] NULL,
    [WastageWeight] [smallmoney] NULL,
    [RequiredMaterialWeight] [smallmoney] NULL,

    [ObsoleteFlag] [bit] NULL,
    [ContainmentFlag] [bit] NULL,
    [StockStatusID] [tinyint] NOT NULL,
    [ModifiedDateTime] [smalldatetime] NULL
    CONSTRAINT [PK_StockItem_StockItemID] PRIMARY KEY NONCLUSTERED 
    (
    [StockItemID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [UQ_StockItem_StockCode] UNIQUE CLUSTERED 
    (
    [StockItemID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Thank you.

  • Without knowing more about what constitutes a StockItem, it's hard to say. However this:
    [Description1] [varchar](255) NOT NULL,
    [Description2] [varchar](255) NULL,[Description2] [varchar](255) NULL,
    [Description3] [varchar](255) NULL,

    Absolutely freaks me out. That's very much an indication of a place where we can do some normalization to arrive at a better database design.
    It's possible that you need to have another table (or probably tables) that let's you define the attributes of distinct types of StockItems, but it's hard to say without knowing more about the requirements.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • FWIW, I've administered/created many tables like this, never saw any terrible pain point.  Some wasted space maybe.  But I am trying to do better, and am pretty sure this is non-optimal.  I think the "right" solution would involve subclassing.  So you'd have a top-level table has only elements common to all stock items, and subclass tables with the specialized attributes.  Fact that you raised the question make me suspect you already know this.  Lots of good articles on this, and I'm pretty sure some more experts will weigh in here.

  • Grant Fritchey - Tuesday, January 9, 2018 6:30 AM

    Without knowing more about what constitutes a StockItem, it's hard to say. However this:
    [Description1] [varchar](255) NOT NULL,
    [Description2] [varchar](255) NULL,[Description2] [varchar](255) NULL,
    [Description3] [varchar](255) NULL,

    Absolutely freaks me out. That's very much an indication of a place where we can do some normalization to arrive at a better database design.
    It's possible that you need to have another table (or probably tables) that let's you define the attributes of distinct types of StockItems, but it's hard to say without knowing more about the requirements.

    +100
    😎
    What is the data used for?

    BTW: Suggest you profile the data and if you have a repetition of non-numerical values, then you should start by popping those into a separate table(s).

  • This was removed by the editor as SPAM

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply