Normalizing Data base design

[Ray]

Hi Everyone
I am driving my self crazy with trying not to duplicate
data I need to create a automotive lookup application for
looking up parts

Year,Make,Model Ect I don't no the write way to do this the
only thing that changes is the year and what parts are used
for that year mite be different then the year before or the year after

Any Help
Thank you
Ray

[whanigsberg]

RE: Normalizing Data base design

Hi Ray:

This must be a common problem for those who deal in inventory--which I do not--so consider what I say to be only a first stab at the problem.

You wrote, "I need to create a automotive lookup application for looking up parts" which I take to mean that you know the make, model, and year and need a filtered lookup of parts showing only the parts for that particular car.

Suppose you had a car table and a parts table. The car table has fields for make, model and year. The parts table has part#, description, make, model, and year. If a part is good for two years, then there are two records for that part. If the part fits three models for two years then there would be 6 records.

Set it up this way and you can filter the lookup based on the parts table using the expression (more or less) of make+model+year so you see parts records only for the car which concerns you.

I wonder what others will say about this problem.

Bill

[BRochford]

RE: Normalizing Data base design

Ray, What Bill has written is pretty close to giving you what you need. The pitfall is having multiple records for a part number. That is almost guaranteed to make you as old as Bill & I.

Here is what I would do (having had some Inventory experience).

Car_Table:
Make, model, year, part_no (no part description etc. just the part_no)

Part_inventory Table:
Part_no, description, On_hand, On_order, cost, sell, UOM and other misc. field such as
Unit of Measure, suggested Order Qty, Vendor, re-order point, date last sale, date last order, maybe 13 months of sales etc.

Your set would have Car_table as parent & part_inventory table as a child linked one-to-one by Part_no.

The thing to watch is how are you going to be updating your car_table? Will you receive downloads from Vendors or what. Think about updating now while you are still in the design phase rather than after you are all finished. That will make your life a lot easier.

-Barry

[whanigsberg]

RE: Normalizing Data base design

Hi folks:

I sure hope Ray doesn't get as old as us anytime soon!

Barry, what you say is interesting. Of course the whole plan sails or sinks depending on whether the files can be kept up to date. I didn't know if there was a parts list available for each car but if there is then the link to a parts file becomes easier.

Having things in two files makes it possible to reverse the join and answer the question, which cars does this part fit (1:N, part to cars).

Who produces such files; must be the manufacturers but do they share them, sell them?

You need such detailed knowledge to write for an industry.

I wonder if so of the orther real inventory management specialists out there will contribute to this. (I have one particular gentleman in mind.)

Bill

[JohnZaleski]

RE: Normalizing Data base design

I believe what we are talking about here is like a simple bill of materials. If I were doing this I would have at least three tables:
1) Car table (car code, make, model, year)
2) Parts table (part number,description etc)
3) car-parts table (car code, part number, quantity)
several sets:

1) car table (parent) one to many to car-parts table linked by car code. This will show all parts a particular car uses.

2) parts (parent) one to many to car-parts table linked by part number. This will show all models that use a particular part ( where used inquiry )

[BRochford]

RE: Normalizing Data base design

I have a client in up-state NY who is in the repair business and he receives parts/price lists from 14 different mfgrs on diskette. Each Vendor is, of course, in a different format. That's why I look so old Bill, really I'm only 22 .

-Barry

[whanigsberg]

RE: Normalizing Data base design

Barry,

You don't look a day over 20. Now I'll tell you one big reason why I look older than my actual age of 15.

The Government of Quebec operates in French. French machines use an extended character set which requires a special code page. But there are several possible code pages that can be used for the French. One is ISO 80.

Now the problem. Different parts of the Ministry of Education use different code pages and we never know which was used to produce a particular file they send us. In fact, different divisions of the ministry are unable to share files with each other (I swear this is true) and use us (an *English* institution) as an intermediary because we have written a universal translator.

I guess every business is like this if you dig deeply enough. Everyone believes in standards and nobody is willing to change.

Bill

[Ray]

RE: Normalizing Data base design

Hi Everyone
Thank you all for your input what I was trying to do was not
have redundant data for the make and model copying that
data several times for a new year but I think this can not be
avoided because their is only to fields for that

Thank you
Ray

[shaun]

RE: Normalizing Data base design

Hi Ray,

You should not think that duplicate data is inevitable, except for old invoices. This only means that you must re-design your tables & relationships until you have it right. For instance, ask yourself if ONE part can be used on MANY models?; can ONE part be used on ONE model, over several years?; can ONE model of car be unchanged over MANY years?; & so on.
You also need to think about what you mean when you say "part". For example, a Ford windscreen wiper unit is a "part", right? Yes, but, if you can also buy smaller units of the wiper unit then this "part" is itself split into further "parts". You should only ever have one instance of each of these, and if you are stuck with this "parts in parts" I will have a further think about it & see if I can suggest something.

Good luck.
Shaun.