Duplicated items in a DB.

[technisup]

Hi, I would like to hear ideas from you to handle the same item but from different providers in a database?

Thanks for your replies.

 

[SkipVought]

Hi,

Could you provide an example of data to support what you described?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[technisup]

Thanks for your reply, this would be the data example:

Product: Mountain Bike
Provider: All Stars providers
Description:.....
Value:......

Product: Mountain Bike
Provider: Bikers Ltd.
Description:.....
Value:......

And so on.

 

[SkipVought]

What do you mean by "handle"

For the example you just posted, show what you mean by "handle".

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Crowley16]

allow duplicates on product name and use an identity for the pk of products...

either that or a composite key, but I would generally shy away from composite keys...

--------------------
Procrastinate Now!

 

[technisup]

What i mean by handle is that i'm just trying to know how in a website that sales products from several and differents providers the same item from a different provider?

Thanks for trying to helping me out with it.

 

[elsenorjose]

Well, first of all, these are not duplicate items. They are unique because each item is provided by a different vendor. It's poor database design for you to store data this way. What you need to do is store the data in tables for each entity, in this example, a product entity and a vendor or supplier entity. You would then provide IDs for each vendor and product. Queries would then join these tables on their keys. This is very basic database design. If you have Microsoft Access on your system, play around with the Northwind sample database to see how to do this. Otherwise, just Google 'database design'. You'll get a ton of results for something like this.

 

[SkipVought]

This is essential information for database design.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[elsenorjose]

I've seen that one before Skip but I took it with a grain of salt because of its emphasis on a specific product. Granted, it's a tutorial in database design and so benefits from real world examples but comments such as "Occasionally, you made (sic) need to denormalize for performance" do a disservice to the relational model. Poor performance is not necessarily the model's fault and can usually be traced to the DBMSs limitations and poor implementation of the relational model.

 

[SkipVought]

elsenorjose, take a breath and relax.

It's Friday. Weekend's ahead.

Litwin, is a respected source. There's lots of good info in the article to introduce newbies to some db design principles.

I'm on a low sodium diet, so a grain or two won't bother me.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ceh4702]

Well if you had part codes that had good naming conventions, then similar parts would show up next to each other. You might want to look at uniform product codes or make the Manufacturer ID part of the part ID. There are many different ways to do this. One way is to hava part numbers like 00-000-000-00 where each sections stands for something. Like tools or oil or wrenches or nuts or bolts or engine. Then you get into the age old parent child part relationships.

Another way is to have completely random or generated part numbers with no meaning at all. Then you have to have foreign keys to give the part meaning.

If you do not like my post feel free to point out your opinion or my errors.

 

[SQLSister]

When I dealt with parts from many vendors, each vendor list was loaded by vendor and if two or more vendors had the same part, they were in the database as separate records. I had a field for vendor part-number, manufacturers part number and an indentity field that we generated and used to link all the related tables together. This way you could search by manufacturer's part number and see all the vendors and their price for the specific price or search just a specific vendor.

One thing you need to do (trust me on this as the system I'm thinking about wasn't designed this way and I had to fix it (well as much as I could we had to let the old bad data stand) is make sure to store current prices in one table and do not rely on that table to grab the prices for inventory records or sales records in the past. This is one place where you must denormalize to preserve the history of what was actually paid for the part at the time it was ordered not the current price being pulled when you call up an old sales invoice.

"NOTHING is more important in a database than integrity." ESquared