Post reply

How to design an optional comment uppon flagging a row for deleltion

  • June 18, 2009 at 8:23 am

    #384797

    Hi All,

    I have database design question. I bet some of you have done this before and can tell me a best practise. But also when you just have a good idea or tought i'd like to know 🙂

    here is my situation:

    I have a table with, let say, products. I never delete a product from my table but if i don;t want them in my application screens any more i gave them a status.

    status 0 means: nothing,.. this is a normal product

    status 1 means: product excluded because product is stolen

    status 2 means: product excluded because it was send back to manufacturer for warranty

    etc., etc.... (ps these statusses are in a separate table, i use a FK on this field)

    now i have a new requirement telling me the user needs to enter a reason for exclusion of the product. My status field is no longer sufficient. How should i model this?

    I was thinking about replacing the status FK with a boolean called "is_excluded" and creating a new table with all the excluded products, there status and reason.

    But somehow this does not feel good to me because now i have a possibility to create 'ghost' exclusions when a records get inserted into the excluded producttable but the boolean is not set or vice versa (boolean is set but now row is created)

    What is the best way to design this problem, what is a best practice?

    I hope to hear from you,

    Thanx!

  • June 18, 2009 at 10:10 am

    #1011902

    Well, you could add a new status meaning "product excluded for other reasons" and only then allow free form characters.

    Assume ProductStatus = 999 means that a text is needed, which must be at least 20 characters in length, and no other ProductStatus may have a reason, they try this:

    Alter table X

    Add ProductExcludedReasonDescr varchar(255) NULL

    go

    Alter table X

    Add constraint X_C_ProductExcludedReasonDescr_Required CHECK

    ( ( ProductStatus 999 and ProductExcludedReasonDescr is NULL )

    OR (ProductStatus = 999 and LEN(ProductExcludedReasonDescr) >= 20 )

    )

    go

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 2 (of 2 total)

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