Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2003
    Location
    USA/PA
    Posts
    6

    Unanswered: using multiple sequence tables

    Hi,
    I'm a newbie to Oracle and I'm working on a small project that requires multiple tables to be linked together via master table. So I need to have a master table foreign key in each child table. To generate the unique primary keys I'm using a sequence table. I get the next sequence number and store it in a session to be inserted as the foreign key in any child tables. I also need a primary key for each child table as well. Is it customary to create a sequence table for each child table then use the
    select [table_name_here].nextval as ID_NUM from DUAL to get a sequence number to be used as the primary key or would it be better to create one sequence table and just get the seq from that table to be used as the primary key for each child table?

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Generally people tend to create a sequence per table, but you don't have to. There is a possibility that if you have many, many tables sharing one sequence and a lot of insert activity going on then you may get contention on the sequence object.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    My feeling is that you should only use sequences when you HAVE TO (ie: 1 or many columns that would make up the PK might be NULL, so a sequence is necessary)

    I've worked on databases where ALL they use are sequences as PKs. The problem I found there was that EVERY table eventually only had numbers in every column and no relevant data! You had to backtrack 3-5 FKs just to figure out what the sequence number represented. Very frustrating.

    Also, with sequences you cannot insure uniqueness of your data. All you are ensuring is that the PK (sequence in this case) is unique which it would always be anyways. With a sequence you could be inserting duplicate rows and not be aware of it. I suppose you could enforce that with a unique index of some sort however.

    If possible, make the PKs relevant by using the actual columns and not a sequence.

    If you must create sequences then you should create a DIFFERENT sequence for each PK/table.

    So PARENT_TABLE uses SEQ_PARENT_PK
    CHILD_TABLE uses SEQ_CHILD_PK
    etc.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Be careful when using "relevant" data as the PK (and as FK to other tables). Many people assume that social security numbers make a good pk/fk, since they are unique, and meaningful at the same time. What they don't realize is that they can change, and when they do, your screwed. That's why I prefer using sequence numbers as pk/fk, and using unique constraints to ensure that the actual data is handled properly. To be honest, I've seen very little data which would never change over time - manufacturing systems switch from short item codes, to long UPC codes; employee id's change when divisions merge; DEA numbers change all the time. And, storing an average 9 character code as the fk in many other tables will use extra storage (although that really is a minor concern with the low cost of disk these days).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Just to throw my 2 cents in, I totally agree with the previous post. Perfect example - I have a good friend who owns his own web-based business (who doesnt these days?). He didnt see any problem using an email address as the primary key for his customer table. I mean each person would have a unique email right?

    He didn't consider what would happen if someone switched ISP's. So now in all of his tables he had to do an update to the foreign keys. Easily could have been overcome by using a sequence. Not like any of his users need to know their customer id's anyway.
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I am talking about a normalized table.
    You are talking about instances where a DESIGN flaw was the problem.

    ie: Oops, I thought email uniquely identified a customer. I was wrong since two people could share an email address and also customers could change their ISPs, etc.

    To me, that is just an example of incorrect design since a customer could definitely NOT have an email address, yet still order something (possible, but not probable).

    So yeah, a sequence is of very good use there.

    here is a good example:
    in 2NF normal form, for addresses, you wouldn't repeat CITY/STATE over and over again, you would create a city-state-zip table and then the customer addresses would only show the zipcode FK's do the city-state-zip table (done in many customer-service centers where they only ask you for your zipcode and then it prefills your city, etc.)

    well, you wouldn't create a SEQUENCE for the zipcodes, you would use the ZIPCODES as the PK.

    this is what my old company kept doing. they kept adding sequences where they were not needed.

    are sequences needed in general? YES in many situations. Evaluate based on normal forms and decide from there.

    2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    bump

    bump
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    bump

    bump
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    hrmmm ... why can't I delete posts?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Yes that is a great example with the zipcodes- I read the word sequences, and my mind stopped reading the rest. I now see that my post was really irrelevant, and with this I am retiring for the weekend.
    Oracle OCPI (Certified Practicing Idiot)

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Using zip codes as a primary key is a terrable idea. I have made many tables using zip code, and it is VERY common for a zip code to point to multiple cities and area codes. The zip code should never be a primary key, use a sequence and have a non unque index on the zip code.

    Zip codes change....
    Area codes change...
    Item number change...
    SSN's change...
    EMAIL address change ..... (curse oracle's forums)
    Everything can change, except a nice non-informative sequence number.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It is VERY common for a zip code to point to multiple cities and area codes.
    A ZIP code only ever "points" to a single USPS mailing area in a single state.
    There is no relationship between ZIP and phone company area code, but
    mentioning this reality contributes nothing to the discussion.
    While it is rare a ZIP can "split"; where some of the old ZIP code addresses get
    placed into a new ZIP. This reality does NOT affect the use of ZIP as the PK
    in the ZIP_CITY_STATE table. It simply means that a new row with a new PK
    gets added to this table. The fact that a ZIP code can & will exist in many
    tables other than ZIP_CITY_STATE has ZERO impact on whether or not the
    5 digit ZIP is used as the PK for the ZIP_CITY_STATE. Adding a sequence number to this table simply takes up more space without adding ANY useful
    purpose.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Apr 2004
    Posts
    246
    I wouldn't make a sequence for zip codes - they're small enough, and static enough. However, I would also store the city along with the zip in the address table - a city with mult zip codes, a zip code covering mult mailing cities. Again, a perfect example of why you don't create sequences for everything.

    Of course, email should be stored in a child table, as a user/cust/whatever may have multiple emails (I know I do).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    the sub codes would be on the customer table.
    the MAIN zip code determines the city and the sub-code determines the specific area of that city. zipcodes for customers can change at will with no ill effects. The zip-code is only the PK on the ZIPCODE table.
    I can't think of any zipcodes changing where the state-city is different that it used to be?

    90210 is still Beverly Hills and always will be baby!

    hey, I was just giving an example.

    If people want a bunch of sequences then go ahead. Fine by me.
    Whatever. People will do what they want regardless of what I have to say.
    Just offering a different point of view.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can't think of any zipcodes changing where the state-city is different that it used to be?
    With growing suburbs, new post offices get built & new "city names" are created.
    The addresses handled by the new PO get a new ZIP & a new City name.
    If ZIP codes were not 100% unique, the automated sorting machines could not decide into which sack to deposit the letter.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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