locked
Pk for a table RRS feed

  • Question

  • I am building a database for a point of sale application , the question is about the table " items " which holds the attributes of all items in the system ( lookup table ) .

    The question is about the unique identifier for each item( the pk column ) , I see that there is nothing wrong if I make the " barcode " column as the pk for the table , but I saw many people create a column named " item_id " I.e , and make it the pk column , and the " barcode " is another attribute .

    Which way is correct in your opinion ?

    Wednesday, July 6, 2016 9:05 AM

Answers

  • >IS NOT " PK " THE SAME AS " UNIQUE IDENTIFIER " ?

    >WHAT DO YOU MEAN WITH " NATURAL KEY " ?

    Every table has one or more "Keys".  One of these Keys may be designated as the "Primary Key", which just means it's the one you use most.  A "Natural Key" is a key comprised of columns that appear naturally in the table, as opposed to an "Artificial Key" which is a number or GUID added to the table, which has no meaning in the domain being modeled, and is there only to identify the row.

    The paradigm case for an Artificial Key is when there is no natural key on the table, or when the natural key has several columns and you want to add a single-column key for convenience.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, July 12, 2016 3:37 PM
  • Based on the limited info available here, you should only have item_id. That will serve as the unique identifier for a particular item in your entire inventory. 

    The other point you should make sure you get clarity on is whether all the barcode types your system will handle can be uniquely represented in a single column of the same type. I only know there are lots of barcode types and some can represent a lot of information (e.g. entire address) while others may represent just a set of characters/numbers. The actual barcodes however are very different visually. Run it by your subject matter expert (e.g. the business analyst) to be sure.


    No great genius has ever existed without some touch of madness. - Aristotle

    Wednesday, August 3, 2016 7:32 PM

All replies

  • Hi eslam_elbyaly,

    Based on my understanding, as long as you can ensure the barcode column is unique you can use it as Primary Key. But when you start using Foreign Key, I think item_id is better as it could be shorter and easier to understand.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Wednesday, July 6, 2016 9:22 AM
  • It's not a " column name " problem , 

    I can name it item_id and use it for barcode.

    I'm asking, 

    Should I use one column or two ?

    Wednesday, July 6, 2016 9:48 PM

  • >>>but I saw many people create a column named itemid  

    Item_Number cannot be an identifier ? You can crate an IDENTITY or SEQUENCE  number to identify the row


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 7, 2016 5:36 AM
  • You originally asked about choosing a primary key - with a choice of a barcode column or some ID column (which you did not define). Well, actually you first used the term "unique identifier" and then "pk" - as if those are one and the same.  That isn't a requirement.  There are reasons to choose a synthetic value for the primary key (or for the clustered index - which again is not necessarily the same as the primary key). In addition, one should always enforce the natural key regardless of whether it is chosen as the primary key.  If not the primary key, one does this with a unique constraint.  In such cases, one can use either value to identify a specific row.

    Now your question seems to have evolved into a question of using a single column or multiple columns in the primary key.  This is a very different question - and I'll guess that you did not intend to phrase it in that manner since that is a very different approach and is based on (your) unspoken assumptions. So I'll ignore that and respond to the original question.

    Which should you choose?  Who can say without knowing the actual system you are modeling.  Typically you find that identity columns or sequence-based columns are used. But you need to ask questions before you decide.  Can the barcode of an item change?  Does EVERY item have a barcode?  How do you propose to store a "barcode" (i.e., what datatype will you use)?  Can the same "item" have different barcodes (perhaps due to different sourcing)?  Quite frankly, do your own analysis before you start asking basic but important questions like this. There are no simply answers and you shouldn't expect "good" suggestions to be offered by strangers with no understanding of your actual system.

    And one last suggestion. Until you identify a need (or at least a good compelling reason) to implement an identity column (or similar) in a table, DON'T. Designate the natural key as the primary key and complete your model.  Do some simple proof-of-concept testing and determine if the model is correct. You don't need to define the complete schema to do this, but you do need to define a critical subset. 

    Thursday, July 7, 2016 1:00 PM
  • You do not want to make barcode your PK on items for many reasons. 

    First, some items may not have a barcode at all. 

    Second, barcodes can and do change over time for an item.   You may or may not want to create a new item because the barcode changed.  You need to create an items table, and a barcode table linking barcodes to items.

    Third, never create a PK on a field the user can change.  You will be sorry in the long run when you have to update foreign keys on 20 tables.



    Thursday, July 7, 2016 1:15 PM
    Answerer
  • IS NOT " PK " THE SAME AS " UNIQUE IDENTIFIER " ?

    WHAT DO YOU MEAN WITH " NATURAL KEY " ?

    Thursday, July 7, 2016 9:22 PM
  • Any help ?
    Tuesday, July 12, 2016 3:09 PM
  • >IS NOT " PK " THE SAME AS " UNIQUE IDENTIFIER " ?

    >WHAT DO YOU MEAN WITH " NATURAL KEY " ?

    Every table has one or more "Keys".  One of these Keys may be designated as the "Primary Key", which just means it's the one you use most.  A "Natural Key" is a key comprised of columns that appear naturally in the table, as opposed to an "Artificial Key" which is a number or GUID added to the table, which has no meaning in the domain being modeled, and is there only to identify the row.

    The paradigm case for an Artificial Key is when there is no natural key on the table, or when the natural key has several columns and you want to add a single-column key for convenience.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, July 12, 2016 3:37 PM
  • No, PK is not the same as unique identifier.  The primary key is a specific type of constraint within sql server.  You can only have one primary key per table.  A column that is part of a primary key cannot be null.

    You can have any number of unique constraints (or indexes). Usually you define these for the set of columns that are your "natural key", and you can have multiple unique constraints.  If I had to guess, I'd say that you really don't have any formal training in designing databases.  That's a problem - because this isn't (or shouldn't be) something you just throw together like one tends to do for application code.  The choices you make will affect your system for a long time - and they can impact the effectiveness of such in ways that you cannot easily anticipate.  Just like you don't start designing and building houses without understanding basic structural engineering, you don't design databases without understanding relational theory and how it is implemented. And, of course, you need to understand the business that you are attempting to model.

    You originally asked if barcode could be used as unique identifier or pk. My knowledge does not extend to POS or barcodes. As I mentioned, you need to know your business first, before you start designing the database. Tom suggests that barcode is not a good candidate for either. That was my suspicion as well. Is there an alternative candidate that already exists in your schema? Since you are discussing keys, you should already have a schema defined for your item table - at least as a starting point. Is there anything there that is unique across all rows? Perhaps, but that is unlikely. So the easiest route is to create/implement a surrogate. Identity, sequence, UID, etc. Which one? Some web searching about surrogate keys would be useful - and perhaps some blue-sky thinking about how you intend to use the system. 

    I guess my primary point here is to worry less about implementation and verify that you understand the business, the requirements you need to implement. Use that to validate your data model - verify that your entities and their relationships are correct.  And you should do this BEFORE you write any code. At least, that's the way we like to dream about such things. And don't expect any particular suggestion to be "perfect" for your system. Something that is perfectly reasonable for one situation may fail in another. 

    There have been many discussions about the choice of a primary key, as well as comparisons of natural keys vs. surrogate keys, and the choice of a clustered index. You can find these with simple searching.

    Tuesday, July 12, 2016 3:57 PM
  • Your question is mostly domain knowledge rather than technical. Many responses so far are diving into one or more specific technical aspects of the implementation which is important but not really useful until your original question is answered.

    Tom Philip's response provides a good set of reasons for not using barcode as your PK. While barcode changes for an existing product/item is rare, it does happen. Even if you're willing to deal with that (not hard to include an admin function that updates all relevant tables when a barcode changes), another major issue is with the types of barcodes in use.

    Most people are really thinking of UPC when they think barcode. UPC is just one of the broadest use cases but it is not the only one plus there are multiple ways UPCs are implemented. Very, very generally, there are linear and matrix barcodes. Within each of them there are multiple generations plus variations depending on its use case and implementation. It gets pretty tricky if you want to have a single column that stores barcodes and is used to uniquely identify a row. Different encoding schemes produces different physical output for the barcode though they maintain the same logical value. Just taking the characters/numbers of the barcode alone is not sufficient. You need to include the encoding scheme associated with that barcode. 

    You can still index on barcodes since some searches are likely to be based on barcodes. However, go with some other column, synthetic if necessary, for your primary key.


    No great genius has ever existed without some touch of madness. - Aristotle

    Tuesday, July 12, 2016 6:13 PM
  • last question if you excuse me ,

    i will use item_id column as the pk for the table , and use another column for barcode ,

    if i use this logic ,

    do i have to include " barcode " column in the stock table , or just include the " item_id " ?

    Friday, July 29, 2016 1:49 PM
  • Any help ?
    Tuesday, August 2, 2016 7:55 PM
  • Based on the limited info available here, you should only have item_id. That will serve as the unique identifier for a particular item in your entire inventory. 

    The other point you should make sure you get clarity on is whether all the barcode types your system will handle can be uniquely represented in a single column of the same type. I only know there are lots of barcode types and some can represent a lot of information (e.g. entire address) while others may represent just a set of characters/numbers. The actual barcodes however are very different visually. Run it by your subject matter expert (e.g. the business analyst) to be sure.


    No great genius has ever existed without some touch of madness. - Aristotle

    Wednesday, August 3, 2016 7:32 PM