Same Items Different Keys?

[anonymust]

How can I make a relationship / table with two item that are the same?

I'm making a database with items found in my local supermarkets.


and two supermarkets sell the same package of frozen salmon, but they have different barcodes

[JoeyB]

Give your products a ID number.
Make a separate table for barcodes and give these barcodes the ID numbers from the products. Now more barcodes are related to one product.

hope it helps

[Demerit]

Create two tables as JoeyB said Say:

TblProducts

ProductID	Autonumber
ProductName	Text
BarcodeID	Number

TblBarcode

BarcodeID	Autonumber
BarcodeName	Text or Number

Link the two tables using BarcodeID and enforce refferential intergrity

Hope it helps

[ItsMe]

@ Demerit,

Your solution would create unnecessary records in tblProducts. The OP states that products with the same description have many barcodes.

[Demerit]

If that is the case, then a Foreign key "ProductID" should be added to TblBarcodes on which the relationship should b created. And BarcodeID should be deleted from TblProducts.

[rpeare]

You'll need three tables to handle your need:

1. A PRODUCT table (autonumber PK is fine) along with any information you want to record
2. A STORES table (autonumber PK is fine) along with any store related information
3. A BARCODES table (autonumber PK is fine as well) a FK to the PRODUCT table, a FK to the STORES table along with the barcode information

the barcodes table is known as JUNCTION table where you are forcing a relationship between two tables that are not linked directly through an intermediary table.

This is the same type of structure I've been trying to get across to Demerit on his own project

[anonymust]

I think I just realized my DB is pretty much useless....

I found that out today when I was scanning the barcode on some banana's that I has to weighed.
Now I have to codes for Banana's.. because the barcode i scanned was the supermarkets code (both codes from the same supermarket) and I cant figure out how the barcode is encoded.

Its not a duplicate barcode, but it will be a double entry for banana's in my DB

I need to redesign my DB from the group back up again.

[anonymust]

Give your products a ID number.
Make a separate table for barcodes and give these barcodes the ID numbers from the products. Now more barcodes are related to one product.

hope it helps

I was thinking about that, but I will be using a form along with my USB lazer barcode reader when filling in data

[ItsMe]

Its not a duplicate barcode, but it will be a double entry for banana's in my DB

Post #6 addresses this issue. Make a special table to manage your barcodes. Each barcode record can have an FK for your product description and an FK for the product's respective store. No duplicate barcodes, but you can have many unique barcodes associated with one product from a single store. This should address the issue you are having with bananas.

The trick is building a form that is flexible enough to manage updating and querying the tables.