January 8, 2018 at 9:17 pm
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.
January 9, 2018 at 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2018 at 7:04 am
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.
January 9, 2018 at 7:24 am
Grant Fritchey - Tuesday, January 9, 2018 6:30 AMWithout 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).
January 10, 2018 at 7:11 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply