Which design is better?

  • 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

  • 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.

  • 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.

  • 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?

Viewing 4 posts - 1 through 3 (of 3 total)

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