Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Advice Please

  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Advice Please

    Hi folks,

    Need any advice on the following solution please.

    I have three departments.

    Department1 lists the equipment that is required
    Department2 lists the same equipment to be ordered with Purchase Order No. etc.
    Department3 lists the delivery tracking dates of the same equipment

    I don't wish Department 2 & 3 to re-enter the equipment details and description etc. that Department 1 already has entered into a VB/SQL Server 2005 application i am trying to build. If Department 1 then deletes a certain equipment listing, it should also be removed from Department 2 & 3.

    Would these goals be achieveable by triggers? Any advice would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One table, no triggers, and the VB application needs to determine three things... The VB app needs to determine which user is running the app, which department that user works in, and which columns that deparment can see/modify.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks for reply Pat.

    I thought of the one table scenario and i am able to restrict columns per department through the VB app etc. but i'm trying to get my head around the following scenario - If one of the departments deletes a record by mistake, it will also no longer show on the other departments view etc. How would you account for this - Lock down delete rights?

    This may pose a problem as Department 2 may also need to add additional items to order that may not be on Department 1's list so they may need a delete functionality.

  4. #4
    Join Date
    Oct 2009
    Posts
    93
    Sorry Pat. Should have mentioned the dilemma in my first post but forgot. That is why i was thinking about triggers

  5. #5
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You've said if department 1 deletes a record it should be deleted from department 2 and 3, so presumably a single table scenario is perfectly acceptable.

    If you are concerned about a department accidentally deleting a record, then you should have appropriate checks within the application to ensure that doesn't happen.

    I make it a practice to never delete a record from a table in a database. Instead I utilise a status field in each record to indicate to the application that the record is no longer current, so as far as the application is aware the record is gone, but from a DB point of view you can investigate the record and have functionality to re-activate.

  6. #6
    Join Date
    Oct 2009
    Posts
    93
    Hi Engada,

    Many thanks for your comment. The Status field is a nice option i must say. I could do a sql statement to only return records marked as "Active" perhaps. Very nice for the delete dilemma.

    The single table may work but i'm getting stuck on a certain part of that design.

    For example, Department 1 lists a Car, now Department 2 needs to order that car but also must also order the relevant car extras (e.g. insurance or documentation) if necessary for that car. (So Department 2 may have mulitple entries per each Department 1 unique listing).

    If Department 2 lists extra items as per the Single Table design, then Department 1 will now also see extra listings in their application which i don't really wish if necessary.

    Any more thoughts on my dilemma would be greatly appreciated.


    Quote Originally Posted by EngadaSQL View Post
    You've said if department 1 deletes a record it should be deleted from department 2 and 3, so presumably a single table scenario is perfectly acceptable.

    If you are concerned about a department accidentally deleting a record, then you should have appropriate checks within the application to ensure that doesn't happen.

    I make it a practice to never delete a record from a table in a database. Instead I utilise a status field in each record to indicate to the application that the record is no longer current, so as far as the application is aware the record is gone, but from a DB point of view you can investigate the record and have functionality to re-activate.

  7. #7
    Join Date
    Oct 2009
    Posts
    93
    hmm, Pat and EngadaSQL you may have solved this dilemma also thinking more about your comments

    Perhaps if i set another status field to show department, i can just pull records for that department and hiding other unnecessary records. Will try this and see how i go

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sullyman View Post
    HThe Status field is a nice option i must say. I could do a sql statement to only return records marked as "Active" perhaps. Very nice for the delete dilemma.
    ...but with serious performance implications on even a moderately sized database.
    Do not use this "deleted flag" option as a default for all situations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about a partitioned index on this field Blindy?
    George
    Home | Blog

  10. #10
    Join Date
    Oct 2009
    Posts
    93
    How do you mean BlindMan?

    When a user from Department 1 clicks on the project treelist for example, all information would be returned by preforming a select query for records marked active for that project.

    If a person marks a record as inactive/deleted, the next time they login to the program, this record will be hidden or if they select this project again from the treelist, the records will refresh and that record will be hidden.

    In the app, if they select another project, then the select query would then run again and return the active records for the new project that was selected.

    Would this degrade performance?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, that will degrade performance.

    Every query for current data will need to scan that bit field, and bit fields make absolutely lousy candidates for indexes.

    I've seen this time after time at many clients.

    gvee's suggestion of partitioning the table might mitigate some of the impact.

    This is why I prefer using archive tables rather than "deleted" flags. Archive tables can store more historical data, and since most of your queries only access current data, you only need to read the less efficiently accessed archive data on rare occasions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2009
    Posts
    93
    I was going to use a varchar field and have a dropdown with Active/Deleted values. Then the Select would be

    Select * from Table where Records = "Active" etc.

    Would this work?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. That will work...even worse than using a bit flag.
    You have a boolean value (Deleted/Not-Deleted). Why are you going to fatten it up with a character string?
    Use a bit value, or a tinyint if you need to do summations on it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Oct 2009
    Posts
    93
    Thanks blindman. I'm only a novice so go easy

    Will the bit value query really slow things up? I will only have active projects in the table. As you suggested, i will have historical data in another table

  15. #15
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by blindman View Post
    ...but with serious performance implications on even a moderately sized database.
    Do not use this "deleted flag" option as a default for all situations.
    I'm not condining using it as an across the board solution for everything - yes archive tables are a much better long-term solution. But it's not always appropriate to move data into an archive table immediately. In the case of what I use it for we have 988 tables in our database as of this point in time, 122 of which have a status code, and after being 'deleted' for a year (some tables longer, some shorter - and I'll admit, some never because they are tiny tables), records are moved to the archive database.

    I haven't noticed any performance degradation since adding this field, but I'm only talking tables with at the moment a max of a few million records and at most <5% of those being "deleted".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •