Professional Web Applications Themes

Primary Key. Is it really always necessary? - Microsoft SQL / MS SQL Server

It sounds like you are confusing storage of data with business rules. Yes, primary keys are important. They allow you to uniquely identify a row. This allows you to update a specific row. It also allows you to create foreign keys to other tables. If you give items away (or discount items for multiple item purchases) then store that within your table where you store information about the order and how much that customer owes you, not within your "materials" table. -- Keith "Dan" <com> wrote in message news:phx.gbl... ...

  1. #1

    Default Re: Primary Key. Is it really always necessary?

    It sounds like you are confusing storage of data with business rules.

    Yes, primary keys are important. They allow you to uniquely identify a row. This allows you to update a specific row. It also allows you to create foreign keys to other tables.

    If you give items away (or discount items for multiple item purchases) then store that within your table where you store information about the order and how much that customer owes you, not within your "materials" table.

    --
    Keith

    "Dan" <com> wrote in message news:phx.gbl... 
    Keith Guest

  2. #2

    Default Re: Primary Key. Is it really always necessary?


    "Dan" <com> wrote in message
    news:phx.gbl... 
    away 

    Then that changes what entity you are storing in the table from a product
    (for that order) to a "line item" or some other fictional entity. That is
    fine, but the primary key for such entities is usually just an autonumber
    integer. So productid and orderid would be foreign-key fields, but not part
    of the primary key of the table.

    David


    David Guest

  3. #3

    Default Re: Primary Key. Is it really always necessary?

    > Now, let's say ny business requirement is such that I need to be able to 
    away 

    Then you could include price in the orderdetails table, and have price as
    part of the primary key... one row would have a price of 0.00, and the other
    would be the actual price? Or, have a different productid (but still call
    it Chai, not Free Chai)... and maybe the products table is tracked slightly
    differently for inventory?


    Aaron Guest

  4. #4

    Default Primary Key. Is it really always necessary?

    Hello SQL Gurus out there!

    I've read many posts here that stress again and again the necessity of a
    primary key for every table.
    I'm wondering if this is always the case.
    Let's take for example the [order details] table in northwind.
    It's primary key is a composite key on orderid and productid.
    Now, let's say ny business requirement is such that I need to be able to
    store 2 rows for the same product on the same order because i'm giving away
    Y free 'chai' boxes for every X boxes purchased and I need to keep history
    of when, how many and to whom I gave it.
    I could add another item 'Free Chai' but that would make stock keeping messy
    and it doesn't make much sense since it's the same item as 'Chai' (redundant
    data). If I have this 'free deal' for all products then that would double my
    products table.
    I could also add a bit column 'ItemFree' to the order details table and make
    it part of the PK but that also seems like a waste since if only a few
    products will have this 'flag' set on. It also won't solve more than 2 rows
    per order (perhaps I will need to sell the first X items at regular price,
    the next Y at 50% off and the next Z at 75% off etc.) so i could make the
    extra column a numeric type with some kind of counter as a part of the PK
    but too will be quite difficult to handle.
    Another option would be to make a seperate 'prices' table and a link table
    between products and prices so that a product could have more than 1 price
    and store it as part of the PK but that will cause redundant data again as
    the price is already stored in order details to keep price history.
    Overall, it seems to me that in this case the [order details] table could
    live happily without a primary key.

    Am I missing something?
    How would you recommend handling a situation like this?

    TIA

    Dan


    Dan Guest

  5. #5

    Default Re: Primary Key. Is it really always necessary?

    1. Yes you need primary keys. There are so many reasons I will not even try
    in this forum. Get a database design book for that.
    2. Compound PK's are bad. This may be a philosophy, but its a darn good
    one.
    Never try to combine two fields into one, it makes programming and SQL
    complicated.

    JIM


    "Dan" <com> wrote in message
    news:phx.gbl... 
    away 
    messy 
    (redundant 
    my 
    make 
    rows 


    james Guest

Similar Threads

  1. Primary Scratch & Windows Primary Paging file?
    By Tommy Oberst in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 14
    Last Post: April 15th, 10:26 PM
  2. Primary Key
    By Javot in forum FileMaker
    Replies: 2
    Last Post: January 25th, 01:17 AM
  3. Add primary key
    By Simon Gorski in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 20th, 11:00 PM
  4. Photoshop Primary Scratch and Windows Primary Paging File Problem.
    By Kris_Kross@adobeforums.com in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 2
    Last Post: October 6th, 10:37 PM
  5. Get Primary Key
    By Leon Shaw in forum ASP.NET General
    Replies: 2
    Last Post: July 21st, 12:03 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139