Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    42

    Unanswered: One sequence per table ?

    Hello,

    I have a database schema with 117 tables.

    Can I have just one sequence for the id fields of all the tables ? Or do I need one sequence for the id of each table ?

    create table address
    (
    id number(10) not null,
    version number(10) not null,
    address1 varchar2(255),
    address2 varchar2(255),
    zip_code varchar2(10),
    city varchar2(255),
    state varchar2(255),
    country varchar2(255),
    postal_box varchar2(50),
    constraint address_pk primary key (id)
    );
    create sequence address_id increment by 1 start with 1 nomaxvalue nocyclE cache 10;

    create table admin
    (
    id number(10) not null,
    version number(10) not null,
    firstname varchar2(255) not null,
    lastname varchar2(255) not null,
    login varchar2(50) not null,
    constraint admin_login_unique unique (login),
    password varchar2(100) not null,
    password_salt varchar2(50),
    super_admin char(1) not null check (super_admin in (0, 1)),
    preference_admin char(1) not null check (preference_admin in (0, 1)),
    address varchar2(255),
    zip_code varchar2(10),
    city varchar2(255),
    country varchar2(255),
    email varchar2(255),
    profile long,
    constraint admin_pk primary key (id)
    );
    create sequence admin_id increment by 1 start with 1 nomaxvalue nocyclE cache 10;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stephaneeybert View Post
    Can I have just one sequence for the id fields of all the tables ?
    I don't see why not, if it satisfies your business requirements.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2012
    Posts
    42
    But generally, how people do when they have a large application with so many tables ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One of our applications (schema, actually) contains more than 1000 tables; not all of them use the sequence, but these that do, use a single one. So - one sequence per schema in this case.

  5. #5
    Join Date
    Oct 2012
    Posts
    42
    @Littlefoot

    If I understand this right, your sequence being incremented on each insert, you end up with id values in your primary keys that are not continuous, like one primary key will receive the values: 1, 4, 12,.. instead of 1,2,3,.. Am I with you here ?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In essence, that is correct. The ID values are of no real value, except to identify records. These IDs are typically never shown to the end users, and mean nothing to the business.

  7. #7
    Join Date
    Oct 2012
    Posts
    42
    All right. Thanks for the feedback. I think I will still go for the one sequence per table primary key. Even though this key has no semantic business wise it still can pop up its tiny head, say in a url or else. I feel like keeping it small.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, if you think that - using one sequence per table - there will be no gaps in ID values, you are wrong.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Considering the ID# in the url for this page is somewhere north of 1.5 million, I would not worry so much over it. Also, considering the IDs that are given out by Google, Amazon, or other large scale service providers, I doubt many people are troubled by long IDs in URLs.

  10. #10
    Join Date
    Oct 2012
    Posts
    42
    Good point. Thanks for the shared thought.

Tags for this Thread

Posting Permissions

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