Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Duplicated items in a DB.
- Thread starter technisup
- Start date
SkipVought
Programmer
Hi,
Could you provide an example of data to support what you described?
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
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!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]- Thread starter
- #3
SkipVought
Programmer
What do you mean by "handle"
For the example you just posted, show what you mean by "handle".
Skip,
[sub]
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!
[/sub]- Thread starter
- #6
elsenorjose
Technical User
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
Programmer
This is essential information for database design.
Skip,
[sub]
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![/sub]
Skip,
[sub]
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!
[/sub]
elsenorjose
Technical User
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
Programmer
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,
[sub]
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!
[/sub]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.
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.
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
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
- Status
Similar threads
- Locked
- Question
- Locked
- Question
