Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Need opinion for choosing Primary Key for my table. (specific)

    Hai,

    For the intro : I'm using SQL Server 2000, and i use client side cursor, and i want to use resync command from ADO. The problem is that the resync needs PK from the table.

    And here is my table:
    1. Sales Order
    2. Order

    The Sales Order consist of:
    1. Order No. (PK)
    2. Date
    3. Client
    4. etc

    The Order table consist of:
    1. Order No. (FK)
    2. No
    3. Item
    4. Quantity

    Now, i want to give the Order table a PK, but i confused what should i use/add to the table so i can have PK. Or is there any other way?

    Maybe this is a very basic question, but please any help would be nice.

    Thank you,
    Ippo

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I don't know your data and your business but at a guess I'd expect (OrderNo, Item) to be unique - assuming Item means a SKU or product code of some kind.

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    Oh yea, sorry that i forgot to explain about the data.

    The Sales Order consist of:
    1. Order No. (PK) = the sales order number
    2. Date = the date that the sales order issued
    3. Client = the name of the client
    4. etc

    For example
    Order No | Date | Client |
    001 1-1-2009 A
    002 2-4-2009 B
    002 4-4-2009 C
    004 6-9-2009 D


    The Order table consist of:
    1. Order No. (FK) = the sales order number from Sales Order (PK)
    2. No = the order of the item on the list
    3. Item = name of the item
    4. Quantity = quantity of the item

    For example:

    Order No | No | Item | Qty
    001 1 handphone 11
    001 2 envelop 5
    002 1 pen 12
    002 2 post it 4
    002 3 mouse 5
    003 1 laptop 2

    so, it's like that.......

    thx

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    From your description, the Order No. and No columns of the Order table are unique so you can use the combination as the primary key.

    If the No (item number) column changes when an item gets deleted or the items get renumbered for some reason this might cause a problem. In that case I would add an Identity column to the Order table and make that my primary key.

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Hai,

    I already resolve this one, i have 2 ways:
    1. Using IDENTITY column (thx for the previous post)
    2. Using Look-up table to set composite primary key.

    Thank you,
    Ippo

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you don't allow a customer order to have the same product appearing more than once on invoice then order no + product no is a natural key to choose
    however some systems require products in a specific order (often the order the customer has specified them) so it may instead be order + row number. this helps the customer because the order is in the sort order they expect to see assuming they marry up your delivery to their internal order.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by Ippo
    Hai,

    I already resolve this one, i have 2 ways:
    1. Using IDENTITY column (thx for the previous post)
    2. Using Look-up table to set composite primary key.

    Thank you,
    Ippo
    Look-up table to set composite primary key ??? Why use a look-up table?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Oct 2009
    Posts
    5
    It looks like you are trying to model a standard order-orderline relationship... the following has a great discussion on the topic:

    Database Design - Many-to-many-Order System

    I might also add that there doesn't seem to be any real requirement/benefit of using a natural composite key here, why not just use a surrogate orderline PK, and keep OrderID as your FK in the OrderLine table?

Posting Permissions

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