locked
What are the good arguments? RRS feed

  • General discussion

  • Hi everybody,

    I am wondering if there is a way to explain my point and defend it among other developers.

    One of our developers (who is C++ very smart person) offered creation of a new table. He suggested the following structure:

    CREATE TABLE [dbo].[tableName](
        [ItemId] [int] NOT NULL,
        [PriceDate] [datetime] NOT NULL,
        [QtyRem] [varchar](10) NOT NULL,
        [PriceInfo] [varchar](1000) NULL,
        [DateModified] [datetime] NULL,
     CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
    (
        [ItemId] ASC,
        [PriceDate] ASC,
        [QtyRem] 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 NONCLUSTERED INDEX [DateModified]
        ON [dbo].[tableName]([DateModified] ASC);
    GO

    I suggested to add descriptions for the table columns and also a Foreign Key constraint into our items table. He is saying there is no need to add bloat for descriptions (making a point this table is not supposed to be "human readable") and also that he doesn't want FK for simplicity. 

    My question is - am I right suggesting adding the FK and if yes, what convincing arguments I can provide to defend my point of trying to make the DB to protect itself and even if that is not an important table, keep it with FK?

    Thanks in advance.

    I made some minor changes in the script to protect the actual table names.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, October 3, 2017 6:41 PM
    Answerer

All replies

  •  > He is saying there is no need to add bloat for descriptions :  May be right considering description being part of Items table and doesn't want to maintain redundant data

     >he doesn't want FK for simplicity :  I doubt that , referential integrity is needed for data consistency. Also, might be worried due to multi-column Primary Key, not sure

    Also,

    • Why to add QtyRem column in Clustered Index ?  By description it looks like column to store quantity and is defined as string type.
    • Why someone needs lone NCI with DateModified column alone?

    Confession: Its his table and he could have some plans in mind. If I were reviewer, I would ask these questions.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by Sarat (SS) Tuesday, October 3, 2017 7:02 PM
    Tuesday, October 3, 2017 7:00 PM
  • I think we initially asked these questions, I may need to dig into my emails for this.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 3, 2017 7:04 PM
    Answerer
  • I think we initially asked these questions, I may need to dig into my emails for this.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Good to know that , if whether or not having FK is your question then answer would be 

    " Having an item in inventory tracking table doesn't make sense when it is removed from Items table "


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 3, 2017 7:33 PM
  • Hello Naomi,

    I think we need foreign key constraints to Items table to maintain refrential integrity so valid ItemId value will be there in table.

    If some item value gets deleted from Item table and its still present in table tablename then that record is not valid.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Tuesday, October 3, 2017 8:51 PM
  • Let me guess that he is the type of C-programmer who feels that his code is self-documented?

    As for foreign keys, my experience is that this can be a hard struggle. "We maintain consistency in the code..." argument. If you can travel forward in time 10 years and show him the crap that *will be* in the database, that might convince him. But I never manage to pull that off, for some reason.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, October 4, 2017 6:50 AM
  • That particular table is supposed to be used for speed improvements and cleared on the regular basis, so I lost the argument, but I am not too upset about it.

    I am currently losing another "battle" which is even more important, IMHO. I'm outweighed 3:1, so I guess I would have to go with the flow at the end.

    The story goes - I posted a bug that we're able to delete rows in one table and introduce orphans. (Nobody apparently cared before). As I anticipated, that bug got assigned to me. And the proposed solution is to add FK constraints, which is all good so far.

    The only little problem is that we're using 0 instead of NULLs for the absent FK. And as a result now I need to insert 0 'Not Assigned' row in our table. I don't think we can evaluate right away how that row may affect all queries used in our applications. Yet that solution seems to be the simplest among 4 other I suggested. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, October 4, 2017 11:33 AM
    Answerer
  • As with most of these questions, it depends and is really a debate.

    If you are referring to extended property descriptions.  That adds very little overhead to the database, and is actually not stored in the table, but in the properties tables.  So it does not affect the table at all.  It is nice to have. Especially if you have something that extracts the data for documentation purposes.

    The FK is up for debate.  It depends on where you enforce the referential integrity.  If you are sure it always exists, or don't care it doesn't exist, or manage the link outside the database in the application, then you don't technically need to add an FK.  But, if you always expect item to exist, I would recommend adding the FK.  

    Wednesday, October 4, 2017 12:13 PM
    Answerer
  • Yes, I was referring to the extended property descriptions (they help me to figure out what each field is for).

    As for FK, this particular new table is created to improve performance. As I understood, it's supposed to keep current prices for our items. It is going to be cleared often, so I think in this case FK is not really that important. Unless someone is going to do something with this table outside the application, we only plan to insert rows for existing items. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, October 4, 2017 2:52 PM
    Answerer