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.



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

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

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...

To...

Barcodes
Primary Barcodes

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


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?

off the top of my head, subject to verification...

primary keys in blue

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