July 19, 2011 at 8:05 am
Hello everyone
Need some advices, i'm building a kind of ERP for my company, and since the beginning that i'm trying to design the database with some kind of "flexibility" to adapt new changes in the requisites of the company, but i can't predicted everything...
Now i'm facing a situation that i don't know what i should do, leave the things as they are, or change the design.
In the "ERP" i have a small module were the employees write the work hours for each day, the work hours are related with one project, and the hours can have several types, stoppage, approval, correction, etc.. At the end of each month the supervisor of each team (have several teams), validate the hours and send them to SAP (yes it's the ERP)... After sending the values, i remove them from the original table that it's normalized and put them in another one that has the final values without ids. In the beginning i thought this was a good way to do the things, they're final values, they won't change, but looks like that i was wrong.
In the past week some changes were made to the personal structure of the company, and some of the hours types need to be renamed, some of the projects will change the name, and now the reports if i apply this changes they don't work the way they should...
So my question is, how should i resolve this kind of situation?
Only have one table with id's and a bit field to identify that it's processed?
But if i change one of the hours type name, for example changing correction to stoppage i always have the last value, and the history it's lost.
Every table in my database should maintain history, and use the one table approach?
Kind of a start date, end date, and a bit field referring to the current active version of the row, this solve one problem, but creates a new one, in every report i have to compare dates to see what's the correct version for some particular date.
Maintain the same logic?
But need to redesign every report each time some hour type change the name, the reports use the hours types as columns, in the beginning i was looking to the hoursTypes table and created a query on the fly, but if i change some names in that table, some columns will disappear from the report.
I know there are other options, but i need some opinions to help me find the right path.
Thanks
July 21, 2011 at 4:37 am
No one? 🙁
July 22, 2011 at 1:30 am
Hi rootfixxxer,
I think you have two choices for this:
1) Put the time name into the time table
2) Create a new table for the project time types
I have used both approaches and found that creating a poject time types, defaulted from the master time types gives both the flexibility and (if implemented properly) allows disparate project time to be rolled up.
So I would plumb for something like the following:
CREATE TABLE [dbo].[tblMasterTime](
[MasterTime_Id] [int] IDENTITY(1,1) NOT NULL,
[MasterTime_Code] [nvarchar](10) NULL,
[MasterTime_Type] [nvarchar](10) NULL,
[MasterTime_Description] [nvarchar](50) NULL,
CONSTRAINT [PK_tblMasterTime] PRIMARY KEY CLUSTERED
(
[MasterTime_Id] 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
CREATE TABLE [dbo].[tblProjectTime](
[ProjectTime_Id] [int] NOT NULL,
[ProjectTime_MasterTime_Id] [int] NULL,
[ProjectTime_Project_Description] [nvarchar](50) NULL,
CONSTRAINT [PK_tblProjectTime] PRIMARY KEY CLUSTERED
(
[ProjectTime_Id] 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
ALTER TABLE [dbo].[tblProjectTime] WITH CHECK ADD CONSTRAINT [FK_tblProjectTime_tblMasterTime] FOREIGN KEY([ProjectTime_MasterTime_Id])
REFERENCES [dbo].[tblMasterTime] ([MasterTime_Id])
GO
ALTER TABLE [dbo].[tblProjectTime] CHECK CONSTRAINT [FK_tblProjectTime_tblMasterTime]
GO
Hope this helps!
Peter
I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz
July 22, 2011 at 6:03 am
rootfixxxer (7/19/2011)
Hello everyoneEvery table in my database should maintain history, and use the one table approach?
Kind of a start date, end date, and a bit field referring to the current active version of the row, this solve one problem, but creates a new one, in every report i have to compare dates to see what's the correct version for some particular date.
You could create a history table for each table. The "live" table will always contain the current record and the history table will contain any changed records.
The history table has the same schema with the addition of it's own PK (probably identity seed) and a HistoryDate column which is the date/time of the changed record.
You then create a view which unions the "live" with the "history" for your reports. The view could look something like this:
CREATE VIEW myTable1_History
as
select
-1 as MyHistoryID,
null as HistoryDate,
col1,
col2,
etc...,
FROM
MyLiveTable1
UNION
MyHistoryID,
HistoryDate,
col1,
col2,
etc...,
FROM
MyHistoryTable1
Designing the view this way will always give you the most current record with a value of -1 making it simpler for the reports and you don't have to struggle with first checking the "live" table then the "history" table.
The history table would be populated by a TRIGGER (FOR UPDATE, DELETE) on the live table.
I hope this helps with your "history" issue.
Steve
July 22, 2011 at 11:28 am
Thanks for both answers.
I have to leave this problem to other time, now i have other priorities.
August 2, 2011 at 12:03 pm
rootfixxxer (7/19/2011)
Every table in my database should maintain history, and use the one table approach?Kind of a start date, end date, and a bit field referring to the current active version of the row, this solve one problem, but creates a new one, in every report i have to compare dates to see what's the correct version for some particular date.
Yes. What you describe is actually the way SCD Type 2 - Slowly Changing Dimensions of the Type 2 - work and it will fit your requirements like a glove.
rootfixxxer (7/19/2011)
But need to redesign every report each time some hour type change the name, the reports use the hours types as columns, in the beginning i was looking to the hoursTypes table and created a query on the fly, but if i change some names in that table, some columns will disappear from the report.
mmmhh... not really. Following dimensional design principles Hour Type should be a dimension table which PK should not be a natural but a surrogate key. Your factual table should include that surrogate key therefore changes on the "name" of the Hour Type would just add a new row on your Hour Type dimension table, no changes to reports of the application are needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply