Results 1 to 6 of 6

Thread: Key conundrum.

  1. #1
    Join Date
    Apr 2010
    Posts
    9

    Key conundrum.

    Hey all, I'm somewhat new to databases (did a bit 7 years ago, but...). And I'm busy trying to code a webstore (deep end etc), and am currently stuck on a barcode table....

    Concept:
    I have a seperate table with basic descriptions of products (mostly video games), however, I can have multiple items with different barcodes which are effectively the same product - say, a release from a seperate region that has identicle artwork and contents, just a different barcode.

    Some though will differ slightly more, with a "Re-Release" version, which is still the same product description, but has different artwork and possibly slightly differing contents (not enough to change a basic description, but enough for me to just record that it's a re-release).

    Also, for either a regular (non) re-release, or a re-release product, I may want to go "Search Amazon for prices this product". I then need a way to single out a single barcode that is most likely to give a return for a single product (the "original" regular release, or "original" re-release, or whatever's most likely to be found in the wild and used by most other stores).

    Therefore, when one product has multiple barcodes for either a regular release or re-release, I need to lable one of them as a primary barcode.


    Code:
    Barcodes
    id	ProductID	Barcode		Re-release?	Primary release?
    1	1		711711		N		Y
    2	1		711712		N		N
    7	1		711715		N		N
    3	1		711713		Y		Y
    4	1		711714		Y		N
    5	2		819110		Y		Y
    6	2		819112		N		Y

    ID = unique id for table contents.
    ProductID is a link to a description of the generic product.
    Barcode is a unique barcode for a product.
    Re-release is boolean, whether the release is a re-release product or not.
    Primary, bool, should be set at least and only once for every unique productid/re-release pair on the most common barcode (ie. prod 1 re-release N, 1 / Y, 2 / N, 2 / Y, 3 / N, 3 / Y etc etc).


    And I'm now having problems figuring out what to use as a key to avoid duplicate entries of Primay...

    I want to limit "Primary" to only happen once for every ProductID / Re-Release pair (as above), however I still want multiple ProductID / Re-Release pairs which are not "Primary".

    Using ProductID, ReRelease and PrimaryRelease doesn't work as I may have two or more entries for the same product, same re-release and not primary (something I realised when adding id 7 above). I could use them, but it would limit me to only two entries for each PrductID/Rerelease pairing.

    Adding the barcode in to a key, although it probably be *a* key, doesn't look like it will prevent there being multiple "Primary" entries per pid/re pair.

    Any ideas how I could go about it? Anyone saying "make a new table" gets no beer, this is already a new table having been split from the products db on other advice, and I'm just getting more confused as I go along

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as an aside, since barcode is unique, you don't need the barcode id, just make barcode the PK of that table

    now for your real question...

    sorry, no can do without another table

    alternatively, you could manage it with application code

    i guess no beer for me

    oh wait, i already gots a beer today

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    9
    ohhh gawd just to get me going, what should i be splitting out in to a seperate table?


    and yeah, you might be right about the barcode key thing I'm sure i had a seperate id key for a reason.... oh well. back to the drawing board...

  4. #4
    Join Date
    Apr 2010
    Posts
    9
    Code:
    Barcode	ProductID	Re-release?	Primary release?
    711711	1		N		Y
    711712	1		N		N
    711715	1		N		N
    711713	1		Y		Y
    711714	1		Y		N
    819110	2		Y		Y
    819112	2		N		Y

    To...

    Barcodes
    Code:
    Barcode	ProductID	Re-release?
    711711	1		N		
    711712	1		N		
    711715	1		N		
    711713	1		Y		
    711714	1		Y		
    819110	2		Y		
    819112	2		N
    Primary Barcodes
    Code:
    Barcode (foreign key)		Primary?
    711711     				Y		
    711713    				Y
    819110   				Y
    819112   				Y

    Ok 1. that doesn't even need a primary flag does it? And 2. It's not gonna stop me putting multiple (re or regular)releases in for a given product. Maybe it's re-release that goes in to a seperate table?....

    To...

    Barcodes
    Code:
    Barcode	ProductID	Primay
    711711	1		Y		
    711712	1		N		
    711715	1		N		
    711713	1		Y		
    711714	1		N		
    819110	2		Y		
    819112	2		N


    Ok already doesn't make sense... primary can only be flagged based on whether something is a re-release or not. Which seriously makes no sense putting *that* in a seperate table.... erm... wtf?


    Almost enough to go back to my original idea of having barcodes stored in product table. product gets extra 4 fields - primary regular barcode, secondary regular barcode, primary re-release, secondary re-release. If I ever need more, I add more fields. Loads of null fields but... Every query to a product will know whether the barcode's primary or not based on which field the barcode matches. Simples. Why am I bothering with extra tables again?
    Last edited by pepsi_max2k; 04-03-10 at 18:04.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    off the top of my head, subject to verification...

    Code:
    Barcode  ProductID   Rerelease   
    711711     1            N        
    711712     1            N        
    711715     1            N        
    711713     1            Y        
    711714     1            Y        
    819110     2            Y        
    819112     2            N        
                                     
                                     
    ProductID   Rerelease   Primary  
       1           N         711711  
       1           Y         711713  
       2           Y         819110  
       2           N         819112
    primary keys in blue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2010
    Posts
    9
    Looks about right

    Trying to figure out how to code a frontend that ensures a primary is always recorded for one product release + re-release though.... In fact, coding *any* of it... *cries*.

    You don't need a job do you, mr 937? EDIT: $90 an hour?!? Err... maybe it ain't so hard to try myself... I think I've used one of my 3 free questions though

    Thanks for the help anyway... If I can't figure it out, at least I have backup plans
    Last edited by pepsi_max2k; 04-03-10 at 18:14.

Posting Permissions

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