Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Stored Procedures -- correct practice?

    Hi,

    I have 3 tables: authors, companies, and countries.

    I have a stored procedure defined as:
    Code:
    PROC addAuthor(AuthorName, CompanyID, CountryID)
        INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID)
    END PROC
    ....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.

    Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example
    Code:
    PROC addAuthor(AuthorName, CompanyName, CountryName)
        SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName
        IF v_numRows = 0 
             -- error
        END IF
    
        SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName
        IF v_numRows = 0 
             -- error
        END IF
    END PROC
    yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.

    What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?

    Thanks for any insights

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Personally, I would continue to use the surrogate keys as parameters.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It depends on what the application has available to it. If you are working with a particular company, the companyID may be one of the session variables in a web session. In that case, you do not need to have anyone (except the computer) memorize any IDs. As an example, look at the URL above. It includes a thread ID. When I hit post, the threadID is used to insert this drivel into the database behind the scenes, instead of the thread title.


    Oh, by the way, while the syntax looks Oracle-ish, the same concept applies to SQL Server. MySQL (Now SUN), or any of the other database engines.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree with the above; use your key fields where possible... Two companies can have the same name, remember... Otherwise we'd use that as our primary key!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can have your cake and eat it. Have the UI display the names to the user, but have it pass the surrogates to the procedure "under the hood". The user doesn't need to know that ComanyABC = id 349920 and SQL Server & the UI communicate using the ID.

    Personally I would constrain the company name as unique. In the real world there might be two companies with the same name but I would want them differentiated in the database, even if only by some uniquifier dreamed up by the user. Better that than risk duplicates of unique company names. I know some disagree with me on that but I ain't budgin'.

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    Thanks guys. All great ideas!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree with you too Poots; but I pointed that out more as a general point applied to this example.

    A better example would be employees(employeeID, firstName, surName)
    You wouldn't pass the firstname and last name instead of the employeeID now, would you
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by pootle flump
    Personally I would constrain the company name as unique. In the real world there might be two companies with the same name but I would want them differentiated in the database, even if only by some uniquifier dreamed up by the user. Better that than risk duplicates of unique company names. I know some disagree with me on that but I ain't budgin'.
    Poots ... I have to respectfully disagree ... let's change a few words and see if it still makes sense.

    Personally I would constrain the user name as unique. In the real world there might be two users with the same name but I would want them differentiated in the database, even if only by some unique qualifier dreamed up by the user. Better that than risk duplicates of unique user names.

    Might not that bother the user?

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It might but IME less so than finding that they can't trust their data due to duplicated entries. The ID is a surrogate key. The name (or perhaps some other field - let's stick with name for now) is an alternate key or at least as close to an alternate key as we have.

    My experience is that if the only unique constraint you put on a table is on the surrogate then you WILL get crap in the table (unless you code up your CRUD routines to check).

    Put it another way - I prefer to err on the over constrained rather than under constrained side of database design.

Posting Permissions

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