Results 1 to 4 of 4

Thread: Database Design

  1. #1
    Join Date
    Jul 2004
    Posts
    15

    Unanswered: Database Design

    Im new to Oracle. Our product now works on MS SQL fine.
    Let us say we have 2 tables Parent and Child with relationship based on ParentId column which is an auto increment identity. We have many users accessing the same table concurrently and everything works fine by using transactions for the inserts.
    I have seen a lot of posts talking about not to use auto increment identity in tables.
    Oracle does not support it so ive thought of 2 options for the long term so that we can move to other databases.

    1. Still use the auto increment as used in SQL but with Oracle change it to just Integer and use Sequence stuff..
    or whatever is the database specific type of getting the auto number.

    or

    2. create an other table which provides us the id by locking down the table everytime we need an id or lock the row incase we use this in other tables..


    Can anyone tell me what is the usual approach to handling surrogate keys and what are the performance implications..

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    change it to just Integer (NUMBER) and use Sequence stuff..

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2004
    Posts
    15
    Are there any performance problems with using Sequences in Oracle or using Auto Increments in SQL ?

    Why not option 2, because then i dont have to worry about the various databases out there. Will that be a great performance hit ?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by DBUser123
    Are there any performance problems with using Sequences in Oracle or using Auto Increments in SQL ?

    Why not option 2, because then i dont have to worry about the various databases out there. Will that be a great performance hit ?
    No performance problems using Oracle sequences, on the other hand, option two:
    Quote Originally Posted by DBUser123
    2. create an other table which provides us the id by locking down the table everytime we need an id or lock the row incase we use this in other tables..
    Does create performance issues.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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