March 5, 2012 at 2:17 pm
From a Database perspective I believe a constraint on the table is more important than having matching code logic
Here is the table that I am coming up with:
CREATE TABLE [dbo].[TableAudit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BusinessObject] [int] NOT NULL,
[PropertyTracked] Varchar (50) NOT NULL,
[OtherInformation] [nchar](10) NOT NULL,
CONSTRAINT [PK_TableAudit] PRIMARY KEY CLUSTERED
(
[BusinessObject] ASC,
[PropertyTracked] ASC
)) ON [PRIMARY]
The problem is this does not match what they want to do in code. They would like logic tracked differently.
If the business object is tracked entirely ie. deleted/update vs a particular property they want the fields separated like this:
CREATE TABLE [dbo].[TableAudit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BusinessObject] [int] NOT NULL,
[PropertyTracked] Varchar(50) NULL,
[ChangeProperty] [tinyint] NOT NULL,
[OtherInformation] [nchar](10) NOT NULL,
CONSTRAINT [PK_TableAudit] PRIMARY KEY CLUSTERED
(
[ID] ASC
)) ON [PRIMARY]
This removes the constraint and also adds a field.
Example of logic in the first model
IDBus ObjPropertyTracked
1 1 Insert
2 1 Update
3 1invoice Number
4 1Paid Amount
In this case the logic is that the property tracked is flexible. Property could be a field or an action. The development team does not believe this is right
The Development Model
IDBus ObjPropertyTracked ChangeType
11 1
21 2
31invoice Number 0
41Paid Amount 0
It is seems just as convuluted to me. I now have nulls and I have to know that a null means I am tracking either an update or insert.
I need to know that I am not crazy. Do I push forward and push my model or should I understand making that property field generic is in bad DB form. There is a child table off this as to why i need that ID field. That will hold the history. The other question is could we have the best of both worlds where I have a select Case in the Get SPs. That would make it look like there model while I still have my integrity. Would I suffer a lot in performance with something like this
Select
Id
,BusObj
,PropertyTracked
,CASE WHEN PropertyTracked=1 then 0 else 1 END ChangeType
From TableAudit
March 5, 2012 at 4:00 pm
I'm not sure I totally understand your situation based on the examples that you have given. Your table design(s) show PropertyTracked as an integer, but your examples appear to have that as a varchar column.
Based on my limited understanding, I do see a potential issue with your design. Will there never be a case where a business object will be updated multiple times thus causing a violation of the PK you have defined on BusinessObject and PropertyTracked? Unless the text "update" is really supposed to represent the OtherInformation column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2012 at 4:17 pm
Sorry about the confusion. I have changed the original to show varchar. These tables are examples and not the real thing. I am trying to represent the idea which I guess I am not doing well.
I do not feel a table should be un-constrained unless it has to be. Meaning something in the table beyond the ID field. It seems that when there is no constraint there is a better chance at bad data. Problem is in this solution the difference in design creates issues in development. I am trying to weigh out if the design is worth pushing for.
March 6, 2012 at 7:31 am
I agree that you should identify a natural key in each table, but I'm not sure you have one in either table structure you have defined.
I'm assuming that this is auditing data changes, and I don't see how you can define a unique constraint on object and property if the property would be 'update', because this would mean you can only update a row one time. At the least you'd need to add a time to the audit trail as part of the unique key.
Can you explain how the process will work? Is BusinessObject an ID for a row in a table? Is PropertyTracked a column?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply