none
is InvoiceDate part of PK of an Invoice Table?

    Question

  • hi friends,

    1. In my Invoice table design I have added, InvoiceNo and SKU as PKs of the Invoice table, but should I add InvoiceDate to that PK list?

    2. Typically what are the columns of an Invoice table of a POS system?

    thanks


    I use Visual studio 2012 Ultimate and SQL server 2008 developer edition!

    Saturday, December 07, 2013 4:36 AM

Answers

  • 1. In my Invoice table design I have added, InvoiceNo and SKU as PKs of the Invoice table, but should I add InvoiceDate to that PK list?

    Hello,

    A primary key is always unique, so in your case you can sell one SKU only once per InvoiceNo; maybe this fits your business rules. Adding InvoiceDate would mean, you can have one InvoiceNo every day.

    Sorry, but without knowing the complete design and your business rules / requirements, it's difficult to give you further advice. In common you have an invoice header (incl invoice no) and invoice details; SKU would be part of the details, not the header.

    Do you know about 3nf normalization? => http://en.wikipedia.org/wiki/Third_normal_form


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, December 07, 2013 5:13 AM

All replies

  • 1. In my Invoice table design I have added, InvoiceNo and SKU as PKs of the Invoice table, but should I add InvoiceDate to that PK list?

    Hello,

    A primary key is always unique, so in your case you can sell one SKU only once per InvoiceNo; maybe this fits your business rules. Adding InvoiceDate would mean, you can have one InvoiceNo every day.

    Sorry, but without knowing the complete design and your business rules / requirements, it's difficult to give you further advice. In common you have an invoice header (incl invoice no) and invoice details; SKU would be part of the details, not the header.

    Do you know about 3nf normalization? => http://en.wikipedia.org/wiki/Third_normal_form


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, December 07, 2013 5:13 AM
  • Use INT IDENTITY as  (SURROGATE) PRIMARY KEY.

    Make the NATURAL KEY a composite UNIQUE KEY:

    InvoiceNo

    SKU

    You don't have to add the date to the UNIQUE KEY because InvoiceNo should be tied to the InvoiceDate.  InvoiceNo & SKU should be unique.

    As Olaf mentioned we need more info on related tables & business logic.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, December 08, 2013 3:35 PM
  • hi friends,

    1. In my Invoice table design I have added, InvoiceNo and SKU as PKs of the Invoice table, but should I add InvoiceDate to that PK list?

    2. Typically what are the columns of an Invoice table of a POS system?

    1. Can a InvoiceNo & SKU combination have multiple InvoiceDate ?  I don't think InvoiceDate is a good candidate to be part of a group key. Why do you need InvoiceDate in the group key?

    2. We can not build Invoice table on the fly. For this one need to understand the business entities, DFD, ER etc for the project you are dealing with.


    Regards, RSingh

    Tuesday, December 10, 2013 12:08 PM