Post reply

Record metadata and IsDeleted?

  • February 16, 2010 at 3:04 am

    #216654

    Hi all

    I don't want to go into too much detail but I am in the process of redesigning our main corporate online resource. In fact, the brief has it now as more of a social networking site for clinical providers of service to provide help to the employees of our customer. At present, the unique users are at around 120K globally. I hope you can help me with your past experiences and can point me in the right direction.

    I have a few questions:

    1: IsDeleted / FlaggedForDelete Flag

    I believe that when a record is deleted, the tables is locked whilst this happens and then released? It also increases the transaction log file size and this maintains a history of sequence number - Is this true?

    And as a result, would it impact a heavy traffic website? At present the db tables number up around the 150 mark and is around 2GB - 3GB in size.

    2: CreatedDate, CreatedBy, UpdatedDate, UpdatedBy

    For something like a social networking database where the content is so fluid, would the following fields and their respective FK references to the User table be overkill OR is this best practice to have these regardless? I am not worried about Optimistic Concurrency now but should I be? The site will have a CRM concept behind the scenes to manage the content, users, moderation and potential areas for new sales. Taking this into consideration, again would the above come into play?

    The only reason I ask, is that we have differing opinions at work, and I need to finalise the design. Many thanks in advance.

    Regards

    David

  • February 16, 2010 at 5:58 am

    #1119212

    Dave Winchester (2/16/2010)

    1: IsDeleted / FlaggedForDelete Flag

    I believe that when a record is deleted, the tables is locked whilst this happens and then released?

    No. The row will be locked, page locks if you're deleting lots. It'll only be a table lock if you're deleting a large portion of the table

    It also increases the transaction log file size

    Only if the log was full. However every single change that happens (update, insert and delete) is logged

    and this maintains a history of sequence number

    Not sure what you mean

    And as a result, would it impact a heavy traffic website?

    Not if the DB is properly designed, the indexes are appropriate and the queries optimally written

    2: CreatedDate, CreatedBy, UpdatedDate, UpdatedBy

    For something like a social networking database where the content is so fluid, would the following fields and their respective FK references to the User table be overkill OR is this best practice to have these regardless? I am not worried about Optimistic Concurrency now but should I be? The site will have a CRM concept behind the scenes to manage the content, users, moderation and potential areas for new sales. Taking this into consideration, again would the above come into play?

    If you have relationships they should be enforced by foreign key constraints.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 18, 2010 at 5:15 am

    #1120811

    Hi

    Thanks for the response. My question about the CreatedDate, CreatedBy...columns - was more along the lines of "Should I have them?" in a social networking site with 100K+ users and where data is changing consistantly?

    Would the querying not slow up then for really large numbers, as showing the primary key for a person who created an article for example, is not much use? Unless you had another column "CreatedByUserName" to speed things up. But then what if the user changes their username?

    Thanks

    David

  • February 18, 2010 at 5:44 am

    #1120831

    Dave Winchester (2/18/2010)

    Thanks for the response. My question about the CreatedDate, CreatedBy...columns - was more along the lines of "Should I have them?" in a social networking site with 100K+ users and where data is changing consistantly?

    I can't answer that question. If you need that kind of information - who created or modified the rows and when - then have those columns. If you don't need that information, don't.

    Would the querying not slow up then for really large numbers, as showing the primary key for a person who created an article for example, is not much use?

    Shouldn't if you're indexing is good.

    Don't try to speed things up by using the username, not unless you've tested and shown that the joins are indeed a bottleneck and are unacceptably slow.

    By using the username not the ID, you've given yourself data consistency concerns without knowing if you're improving performance or not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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