Duplicate Record Check?

[Anuviel]

I have an inventory file that amongst many things has an Item table and a Product table.

Item table contains information about the item such as cost, vendor, vendor code, item code etc.

Each record has an unique record ID (auto-enter serial) however a couple of items can share same item ID.

Reason for this is that the same item can be obtained from couple of different sources.

So for example item Case could have 3 records:

Record 1.

Record ID: 001

Item ID: CS001

Name: Case

Cost: 1.25

Vendor: Vendor1

Record 2.

Record ID: 002

Item ID: CS001

Name: Case

Cost: 1.20

Vendor: Vendor2

Record 3.

Record ID: 003

Item ID: CS001

Name: Case

Cost: 1.50

Vendor: Vendor3

I have a calculation in place which checks for duplicate records so that the same item ID and same vendor cannot be used if a record of it already exists, in other words you cannot create a record for the same item from the same vendor twice but you can create as many records for the same item from different vendors.

I have however run into an issue when someone creates a record for a totally different item using an existing item ID but a different vendor.

I would want to prevent that but do not know how? Any pointers in the right direction are appreciated.

What I want to prevent is this:

Record 1.

Record ID: 001

Item ID: CS001

Name: Ball

Cost: 1.25

Vendor: Vendor1

Record 2.

Record ID: 002

Item ID: CS001

Name: Case

Cost: 1.20

Vendor: Vendor2

I thought adding the name into my duplicate record check calculation however that will not work as there are couple of people using the db so that causes some user issues when naming stuff. Also different vendors might name items differently even though they are the same item.

Thanks.,

[comment]

You should have a table of Items, where each item is unique, and a related table of item instances.

[Anuviel]

Hm, have not thought of that. I am not sure I am following, so if I had a table of items every item ID would be unique in that table but in the instance table I could have more items with the same item id?

[comment]

Items:

ItemID: 1

Name: Case

ItemID: 2

Name: Ball

...

ItemInstances:

InstanceID: 1

ItemID: 1

Vendor: Vendor1

Cost: 1.25

InstanceID: 2

ItemID: 1

Vendor: Vendor2

Cost: 1.20

...

[Anuviel]

Ok, starting to form a picture of how to accomplish that.

Thanks a lot.

[comment]

It's just a matter of normalization. You can see that your structure violates normalization, because renaming "Case" to "Briefcase" cannot be done by modifying a single value.

Note also that, for the same reason, the ItemInstances table should really be a join table between Items and Vendors (assuming a vendor can have multiple items). So perhaps VendorItems would be a better name for it, and it should look more like:

VendorItemID: 1

ItemID: 1

VendorID: 1

Cost: 1.25

VendorItemID: 2

ItemID: 1

VendorID: 2

Cost: 1.20