Operation status reporting table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Operation status reporting table

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2009-07-02 : 13:13:10

If anyone has suggestions for improving my current design I would appreciate it. It may be OK, I'm just not sure.

Our OLTP system has the following tables.

--First a jobs table
CREATE TABLE [Jobs] (
[Job] [char] (8),
[JobClassification] [char] (4) ,
[JobType] [char] (1) ,
[MasterJob] [char] (8) ,
[StockCode] [char] (30) ,
[Warehouse] [char] (2) ,
[Customer] [char] (7) ,
[JobDeliveryDate] [datetime] NULL ,
[JobStartDate] [datetime] NULL ,
[ActCompleteDate] [datetime] NULL ,
[Complete] [char] (1) ,
[QtyToMake] [decimal](10, 3) NULL ,
[QtyManufactured] [decimal](10, 3) NULL ,
[SalesOrder] [char] (6) ,
[Route] [char] (1) ,
[SalesOrderLine] [decimal](4, 0) NULL ,
CONSTRAINT [JobsKey] PRIMARY KEY CLUSTERED
(
[Job]
) ON [PRIMARY] ,
) ON [PRIMARY]
GO

--Then a job operations table
CREATE TABLE [JobOperations] (
[Job] [char] (8) ,
[Operation] [decimal](4, 0) NOT NULL ,
[ICapacityReqd] [decimal](10, 3) NULL ,
[RunTimeIssued] [decimal](7, 2) NULL ,
[OperCompleted] [char] (1) ,
[PlannedStartDate] [datetime] NULL ,
[PlannedEndDate] [datetime] NULL ,
[ActualStartDate] [datetime] NULL ,
[ActualFinishDate] [datetime] NULL ,
[WorkCenter] [char] (6) ,
CONSTRAINT [JobOperationsKey] PRIMARY KEY CLUSTERED
(
[Job],
[Operation]
) ON [PRIMARY] ,
CONSTRAINT [JobOperations_Jobs] FOREIGN KEY
(
[Job]
) REFERENCES [Jobs] (
[Job]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO

In our reporting database I made a single table to report our efficiency (basically ICapacityReqd/RunTimeIssued which become ExpRunTime/ActRunTime in the reporting table) using reports with various parameters such as the job customer, and/or customer group and/or operation actual finish date; or the operation work center and/or the week ending date of the operation actual finish date;etc, etc.

--Here is the reporting table I made
CREATE TABLE [OpEfficiency] (
[Job] [varchar] (8) ,
[Operation] [decimal](4, 0) NOT NULL ,
[StockCode] [varchar] (30) ,
[ProductClass] [varchar] (4) ,
[Customer] [varchar] (7) ,
[CustomerGroup] [varchar] (20) ,
[SalesOrder] [varchar] (6) ,
[SalesOrderLine] [decimal](4, 0) NULL ,
[QtyManufactured] [decimal](10, 3) NULL ,
[WorkCenter] [varchar] (6) ,
[ExpRunTime] [decimal](7, 2) NULL ,
[ActRunTime] [decimal](7, 2) NULL ,
[JobCompl] [char] (1) ,
[JobComplDate] [datetime] NULL ,
[JobComplYr] [int] NULL ,
[JobComplMth] [int] NULL ,
[JobComplWkEnd] [datetime] NULL ,
[JobComplMthEnd] [datetime] NULL ,
[OpCompleteDate] [datetime] NULL ,
[Team] [int] NULL ,
[Archive] [bit] NOT NULL CONSTRAINT [DF_OpProductionEfficiency_Archive] DEFAULT (0),
CONSTRAINT [PK_OpEfficiency] PRIMARY KEY CLUSTERED
(
[Job],
[Operation]
) WITH FILLFACTOR = 85 ON [PRIMARY]
) ON [PRIMARY]
GO

--Here are indexes on the report table.
CREATE INDEX [ix_OpEfficiency2] ON [dbo].[OpEfficiency]([JobComplMthEnd], [Customer], [ProductClass], [CustomerGroup], [WorkCenter], [Team]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [ix_OpEfficiency_ArchiveJobOp] ON [dbo].[OpEfficiency]([Archive], [Job], [Operation]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [ix_OpEfficiency_JobComplDate] ON [dbo].[OpEfficiency]([JobComplDate]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [ix_OpEfficiency_Customer] ON [dbo].[OpEfficiency]([Customer]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [ix_OpEfficiency_CustomerGroup] ON [dbo].[OpEfficiency]([CustomerGroup]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [ix_OpEfficiencyWorkCenterOpCompleteDate] ON [dbo].[OpEfficiency]([WorkCenter], [OpCompleteDate]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

Each night I run a job that pulls data from the OLTP tables above and loads new records into the reporting table. Based on different criteria the script loads the additional attributes like CustomerGroup, JobComplWkEnd, etc.

Some of the main questions I have are -

Should I be storing derived date values? Or is there a better way to enable queries by week end dates, month end dates, etc.

Do you see any improvements that could be made to the indexes?

Should I consider additional tables containing pre-aggregated data instead of using a single table?

Does the order of the columns in the table make any difference?

My reason for asking these questions is because some of the reports seem to be running slow. The problem could be the queries but I just want to confirm the table design.

Thanks in advance,

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 13:26:59

some of things that i would have made different

1. I would have kept an int identity column in all tables like JobID, JobOperationID as primary key as this makes maitainence easier as well as maintains uniqueness without much effort.
2. Assuming Complete dentoed whether job was complete or not, i would make it bit datatype with 1 as complete and 0 incomplete
3.I would prefer int rather than decimal](4, 0)

Also regarding reporting
1. I would prefer deriving month end date,week end dates etc rather than storing them also in table
2.I would decide on indexes only based on what all columns you basically used to query in your reports and also on frequently used filter conditions.
3.The decision of additional aggregated tables depends on how complex you logic is. If logic is complex, its better to apply it once and keep it stored in additional table rather than calculating on the fly each time.
4. order of column in table or ordinal position does not make a difference, you can always retrieve columns in order you want in select query
5. To understand why reports run slow, check the query behind and analyse execution plans as well as reads,writes etc . then add appropriate indexes and/or rewrite query to make them optimised.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources