Database for Packard Parts

[Guest Rojo22]

I wanted to start this thread because I was posting database stuff in the FlamingO thread, sorry to whomever started the thread, I did not mean to hijack it!

I have built a Microsoft Access database that has all of the parts listed in the official Packard Parts book for 1951, with supplementals (this is an original Packard book). The cover is a buff color paper with green letters and designs on it. The orginazation of the database and the design have been completed and about 80% of the part numbers have been entered. The design was built to include some modern conveniences that we can all relate to when we go to search for part numbers. This is not meant to be a static effort to represent the parts book as it is presented in print. A scanned in copy would not allow you to search for a part number. This database will be dynamic, in that you will be able to search for a part by number and bring back the description for the part. This will include a digital picture of the part when possible. You will be able to do more flexible searches as well, and I will describe those when it gets closer to implementation.

This thread will attempt two things. One is to be an update for where I am and how it is going. Second, I would like to hear from you the members as to what you might want to see in the finished product. You can ask for anything, whether it can actually be done will remain to be seen. But certainly any feedback even critical is greatly appreciated.

I will try to address the first goal and then turn the thread over to you guys to post some ideas and feedback. I may also post some questions in here for the folks who have experience relating information out of the parts books and into real life. The database is currently designed and built in a format that will allow me to export to a major database later on. For those of you who are interested, the database is now in Microsoft Access, but can be easily exported out to several other products (My preferences at this point have become Oracle, or perhaps MySQL, I havent decided yet), but dont let the fact that this is being developed in Microsoft Access fool you into believing this database will not be robust enough. I have done development for a major cellular provider in Atlanta, Georgia in Microsoft Access, and they have been using it for over a year without issue, and they are approaching over a million records in the database, and regularly have 5 plus users logged in at the same time. This database is not going to be something that is slow, or hard to use, It has been built with serious production type qualities. Enough so that I feel it could be a commercial product (dont worry it never will). Another reason for the Microsoft Access pedigree is that it is what I have right now and it is easily adaptable for export to other products. I have built a front end for the database, that is the VBA front end through Access, and that is what I use to test the database with, but it is not professional enough for a good website. This is what I will work on the hardest, and I have a few professional contacts that owe me a few favors (Packard trunks hold more than one body) that I will be calling in.

Sorry to bore the non technical people with the litany of details, but the technical people will be much more informed. And for the technically challenged, this is not going to be something that will take very long to get used to. If you use a money machine, you should have enough qualifications to use it.

So there it is in a nut shell, and like I said, I got thick skin, so feedback is good. I might even take some screen shots of the prototype and post them here. I am currently on a work contract away from home though, and it would be a few weekends from now before I actually get home, so it may be a while before I get something in here for you to see, but I will post something to look at.

[Guest Rojo22]

Brian

All excellent points! I WISH I could get a copy of a parts database, and have the knowledge to tear the code apart and customize it for our use. I run out of experience and expertise somewhere in the middle of that excercise!

I have some very good OCR that I have used for other things, my father does geneology research, and he needs old documents scanned and saved for research sake. It is much easier to scan it, proof it, and then save it as a searchable document for research. I probably have saved over 100 documents and saved them into word and PDF files.

I agree with you on the client side of the problem with PDF. A simple PDF reader is available though through Adobe, and it allows the client to handle the files with most functionality being there. The advanced searches and indexes can be built into the document though and can accomodate several advanced searches, whether through the viewer, or if we chose to use our front end to supply that functionality. Those are going to be issues that I try to resolve BEFORE implementation. I promise though, that simplicity and the ability for EVERYONE, regardless of their computer skills, or software will be able to use the database.

I am excited at the expansion of the idea with the service counselors and other information. Keep the good ideas coming, and make sure my vision is grounded. I appreciate your time and expertise in your response.

[Guest Rojo22]

Brian

The front end of this application will have the ability to search not only for part numbers, but will also be able to search for names of parts, categories for the parts and such. It will not be limited to just the part number. If I can get enough indexes set in the initial years that I enter, I also want to be able to do cross year searches for interchangeable parts. One of the toughest things for me to do is see a part and know whether it fits my application or not. Another frustration is someone on Ebay listing a part by number and not knowing what it "really" is. So to kind of give an overview, you should be able to limit your search for a part by name, category, number, and a few other criteria. It will not be just by part number.

I have an understanding of the parts software and how powerful it is for the new dealer. The line drawings for the 1951 parts book is pretty inclusive for most of the parts. These are exploded diagrams with different parts categories described. My ultimate goal would be to have the part number on the parts diagram be a link to the part. What I would do is actually scan in the line diagrams, and have the part number highlighted as a clickable link. When clicking on the part number it would actually pull the information for that part out of the database and provide the information on the screen. If I ever get to this level, I think we would have a tool that would indeed make Packard collecting and restoring more fun and exciting, and at minimum we would have supplied information to Packard enthusiasts. I believe we would be pioneers as well, I have never seen this type of approach for any other make or model.

I am not sure about the commercial viability for the product. If the database proves its mettle in a web environment, and the Packard enthusiasts find it helpful, I would consider having some links for commercial purposes, but this brings along with it a committment level that is hard to sustain. There are a few places to go and look for Packard parts, and places that sell the cross referenced parts, and we could provide and overall link to those, but to actually provide a link from the parts description to a commercial site would bring with it some loss of control.

I would love to see some screen shots, and see what you think are essential functional processes for the application. The front end is really up in the air right now, because that is not my specialty. The back end is really the focus right now. If you can be very specific about the functionality you need (like spell it out for me "I need to look up the part by part group", or "I dont know the part number but I need to be able to see a diagram and determine what part to look up"), it would be helpful. My professional career revolves around this type of work. I spend months with users determining what they need and creating documents for developers to use to actually program the system the users will need. So lay it on me!

The cross reference list is definitely on the radar scope. Ever since I heard about it, I had plans to incorporate it with the database. If the cross reference data is constructed properly, I can just import it in and make it a link to the actual part number as a "See Alternate" link and the user will click on it and show some other part numbers for the same part with different manufacturers.

Thanks again for the input. More thought and feedback will make for a better end result.

Robert

[RO]

See '55-'56 cross reference thread above started by Keith Lumus and contains a whealth of data on parts for '55-56 Packards but there may be other years thrown in as well.

[Twitch]

Yes, the cross-reference is more meaningful than the original Packard numbers. At least that's what I care about on my 23rd series, whatpart can be substituted.

[Guest Albert]

This is the list so far that i have come up with , but still have more books to add, it's in excell format, if any one else has something else to add let me know. Thanks Albert Skinner Toronto 54 Panama, Clipper Special, Patrician (all 54's) Updated Saturday Jan 22

270130-Parts.zip

[Guest Albert]

Here is the Flatrate manual 21 series to 1954 in Word, Added 5 missing Pages, Jan 22 Albert

270134-Flatrate.zip

[bernardi]

I'm constantly amazed at the information that is available on this site! Many thanks for your efforts, Albert.

[Guest Albert]

The flat rate manual may have a couple of sections missing, and no it was not scanned in, I typed it in by hand at work between jobs. I have added a couple of more things to Both files.

[Twitch]

Thanks Albert, you sent me the x-reference on Excel before- very handy. I just received the 1952 Chilton Motor Age Flat Rate & Service Manual from an Ebay bid of $15. It's over 500 pages and just tons of stuff for cars from 1940-52. It's so good I could cry.

[Guest 51Patty400]

Hey Albert, Which one is the yellow/cream one in your driveway? Was past your place the other night very late so I cruised on down your street.

- Mark

[Guest Albert]

That is the Panama we Picked up in Pittsburg in November from E-bay for $375. I'am still waiting for a warmer day so i can but the motor back together as i'am in the process of doing a valve job, have all the valves lapped back in , just have to reinstall them, but it took a while to get the motor unstuck since the car was parked in 1969 after it was involved in an accident. We towed it Back on a tow bar, round trip took us 16hours , we left Toronto at 3am and got back 7pm the same day.

[Guest Albert]

I just figured by typing it in like this i would not have to worry about getting my greasy finger prints on the original, and i could also share it with other who may also find this information usefull. After all i think out #1 goal here is to keep "ALL" of our Packards running and help out those other who are trying to do the same. I would also like to continue adding more part# to the Excell Spreatsheet as I find the information, as again we can all make use of it in one way or another. here is another attachment, the start of the Flat Rate Manual.

270492-Flatcove.zip

[Twitch]

There are 2 Chilton manuals on Ebay- a 1953 which will go back 10 years and a 1960 http://cgi.ebay.com/ebaymotors/ws/eBayIS...mp;amp;tc=photo going back to 1950 covering all real and pretend Packards plus other makes. <img src="http://www.aaca.org/ubbthreads/images/graemlins/wink.gif" alt="" />

[Guest Rojo22]

A little update on the progress of the database. I have managed to enter in the 3708 part numbers, and am working on getting all of the Group Numbers and Group Descriptions in a table. With this information completed, I will begin building my three master tables which will be the workhorse of the database. I wanted to run the attributes that I have given a part by everyone and give a chance to add attributes that you think will be important. Keep in mind I have normalized the database (broken tables into the smallest possible components), so if you dont see something directly associated with a part, it may be in another master table. Let me know what you think needs to be associated anyways, and I will leave messages here to let you know if they are covered in another table or not. Here are the attributes for a part in the database:

[color:"red"] Part Number - Original Packard Part number, [color:"red"] Description - as given in the parts manual, [color:"red"] Number of Parts used - as outlined in the parts manual, [color:"red"] Part Group Number - as outlined in the parts manual, [color:"red"] Chassis Designation - Which chassis the part is applicable for as outlined in the parts manual, [color:"red"] Picture of Part - This will come from a variety of places, [color:"red"] Cross Reference Number - This is to allow the link to the cross reference table, [color:"red"] Corrected Part Number - This is to account for any changes to the part number over the years, [color:"red"] New Part Number - This is to account for a new part superceding the old part, [color:"red"] List Price - an original price given for that year for the part.

I am expecting to have all of the sub tables complete in the next month or so, and have the master tables built shortly after that. I will then build a test front end, just to be able to test looking stuff up, and then I will try and find someone to build a more complete front end for public consumption.

Again thanks for all the feedback and keep it coming.

[Guest JT]

Rojo22, what years are you covering in your database?

[WCraigH]

Rojo22:

Sounds like a real PRO design! I have a couple of suggestions, having designed and implemented a *few* databases myself.

How about a COMMENT column? To save space, the COMMENT could be a subtable with just a code in the master table. The comments could be things like "commonly available" (also link to Xref), "rare", "unbreakable", "fragile", "only available in black", etc, based upon owner feedback or other reliable sources.

Also, an OPTION_CODE column could relate the part number to a specific option like power steering. Then for a specific option code, one could produce a "bill of materials", i.e., all the part numbers related to that option. This would be NULL for generic or common parts like screws, etc.

Think about adding a FUTURE_USE column whose name could be changed when *we* think of something else to add to the master table. Easier designing it in than expanding the master table later.

Keep it up!

[Guest Rojo22]

JT the initial data is for 1951. I plan on moving forward to 1955-56, and then come back and do 1950 and earlier. I have the parts manuals for those years, and since that is really what I collect, that is where I started. I would like to get EVERY year in if I live that long...LOL....

[Guest Rojo22]

WCraigH

Thanks for the feedback, and it is nice to know someone in here has some additional experience to sanity check my design. I welcome your feedback and suggestions. I have added all of your columns, but was way ahead of you in the "Future Use" column. I have a few columns that have been added for that specific reason. I do have a few questions, and would like to throw it out to everyone, as I feel the columns you have suggested will add value, but they will also add some responsibilities as well.

First question is about the comments section. How do you think the approval process for the 'comments' for the part should work, and if there are multiple comments how will they be handled?

I love the Options Code and feel it will add some cool options, especially for those who are mechanically challenged like myself. The parts table will be linked to a 'cars' master table by the chassis designation. An attribute that can be related to the table is the option code. My question is what process will be used to relate an options number to a specific group number? I would think that we would have to have someone knowledgeable go through and relate all of these things. I think we can get there, but it would require a Herculean effort by a very knowledgeable person.

So I have added all the suggested columns, and they can be implemented in the design with no issues, other than the effort to come up with the links for option code.

Lastly, and I wanted to make sure I wasn't alienating anyone out there. I have very strictly maintained the integrity of the parts manual, and interpreted it very strictly to what is there. I have taken no liberties in making the data different or modern. I just wanted to make sure that if we add the additional functionality that a purist is going to be turned off by additional functionality. It would be added with the sole intent of making things easier for us and future generations to preserve Packards.

Again thanks for the feedback and keep it coming. If you are reading this thread and are confused about databases and the technical jargon, I do apologize, but if you can admit it here publicly that you don't understand something, I will go out of my way to explain it the best I can. I have seen the patience most of you have when explaining mechanical things to us beginners and wanted to extend the same courtesy.

[Guest Rojo22]

BH

Thanks for the feedback. Actually the database right now will have three master tables. They will be "car", "part" and "appearance" tables. The tables are built around the attributes of the individual components that make up an entire automobile. The tables are designed to offer flexibility in how we can do some lookups and present information. The cross reference table will be a table that is related to the master "part" table and could potentially grow into a master table.

One thing that is confusing when you look at the parts book, is an error in a parts number. This should be treated differently than a new part being advised. The difference is splitting hairs really, but I wanted to account for both. If a part didnt work, or was re-engineered and a new part was given for it, There will be a new part number for the part. If a part number was corrected, there will be a corrected part number for the part.

If there is enough interest I will go ahead and post the entire database schema (data elements in each table) here and perhaps that will shed some light on what we will be able to accomplish with the data.

[WCraigH]

<div class="ubbcode-block"><div class="ubbcode-header">Quote:</div><div class="ubbcode-body"> (snip) First question is about the comments section. How do you think the approval process for the 'comments' for the part should work, and if there are multiple comments how will they be handled?</div></div>

My suggestion is that we posters at this DF are the "knowledge base" for comments. Input will come from any and all. Approval as a legitimate comment should be defacto unless someone takes issue. Then a topic-specific thread can hammer it out.

<div class="ubbcode-block"><div class="ubbcode-header">Quote:</div><div class="ubbcode-body"> I love the Options Code and feel it will add some cool options, especially for those who are mechanically challenged like myself. The parts table will be linked to a 'cars' master table by the chassis designation. An attribute that can be related to the table is the option code. My question is what process will be used to relate an options number to a specific group number? I would think that we would have to have someone knowledgeable go through and relate all of these things. I think we can get there, but it would require a Herculean effort by a very knowledgeable person.</div></div>

I think that the parts exploded view drawings (plates) in the parts book are the primary authority. For instance, plate 1 shows group numbers for the rare, optional factory air conditioning. So all part numbers listed under those group numbers would be option coded. For instance, the A/C "Accessory Production Symbol" is "AIR" for 1955 and "AC" for 1956. Since they are different by year, these could also be the database OPTION_CODE. In the case of the "Duct Assy" tube, the part number is 469832 "All 55th; 56th...LEFT, USE WITH AIR CONDITIONING". Since this applies to both years, I think the easiest solution is to have a "code convention" resolving these common optional parts with a compound OPTION_CODE, such as "AIR-AC". Then the DBMS SELECT statement could be SELECT * FROM <partsDB> WHERE OPTION_CODE = "AIR" or OPTION_CODE = "AIR-AC"... The formal method for this entails a separate Bill of Materials table, but since this database is for the convenience of Packard folks, we can put up with some "tricks", IMO.

There will be options for which there is no corresponding plate illustration. In this case, we rely on the DF "knowledge base" as the authority, with the same resolution process as I described above.

<div class="ubbcode-block"><div class="ubbcode-header">Quote:</div><div class="ubbcode-body">Lastly, and I wanted to make sure I wasn't alienating anyone out there. I have very strictly maintained the integrity of the parts manual, and interpreted it very strictly to what is there. I have taken no liberties in making the data different or modern. I just wanted to make sure that if we add the additional functionality that a purist is going to be turned off by additional functionality. It would be added with the sole intent of making things easier for us and future generations to preserve Packards.</div></div>

A noble cause! And considering that not every Packard owner *bothers" to buy a parts book, an online version would be handy.

[Guest Rojo22]

WCraigH

Thanks for the insight. I will consider the option code and comment section resolved with your suggestions above, unless I hear other opposing opinions, and then we can come up with the best way to handle it. As for the "tricks", I already have a table for option codes built. It is cross referenced by year so as to have a unique descriptor for each year, so I think we are thinking alike. Again a knowledgeable person will need to spend some effort interpreting the option codes and the actual group numbers associated with them. I am trying not to use any light weight tricky tactics, I would like to see this thing really perform up to commercial standards. I have some high expectations!

Keep the feedback coming along. Will be working on the database all weekend.

[Guest Albert]

I just found an air lift shock to fit the 54 Packard, Monore has listings for the 47-56 Packards in there catalogs but no listing for an air lift type shock... But they do list a 20830 for the back which has a 20.750 extended length and a 8.5 inch travel, by looking in the back of the book there is another shock that has the same ends and travel but is 1/8 inch longer which should not make any difference it has a part # of MA739, I will try and copy down all of the Packard listing and add it to the database as well. They cost me about $90cdn for the pair with the mounting kit and hoses. Maybe this weekends project will be installing them.

[Guest Rojo22]

I spent some time this week in getting the data structure together and linking some of the tables together and it seems this schema is going to work out well, barring any crazy stuff I find in future or past parts books. I did spend some time going through my library of parts books, repair manuals, and other literature. I have come to the realization that I have "official" parts books for the 24th series, and for the 22nd series. These are leather bound books that a dealership probably had to use for the parts department. I have some other books, but they are not "official".

Here is my question. I have searched and found a parts book listed on the PackardClub.org website that includes years 48-54. I am not sure of the contents of this book and wether it would provide the same information that the "official" book provides. My concerns are the relations of the parts information and the plate pictures with parts groups and items that would be listed in the "unofficial" book. If you have a copy of this book I would appreciate a comment telling me what you think of the book, and if you can scan a page in for a picture, I would find this most helpful. I do NOT want to use a unofficial book for some years, and official books for others if the information is not going to be the same.

Next question is, does anyone have an outlet to get these "official" parts books, or exact reprints? If you have a source please let me know. I would appreciate any information. I do not mind buying the books to use, but I want to be sure what I am buying and what I need to enter the parts numbers is one and the same. Any other solutions to this dilemma are also appreciated. I currently take the real parts books apart (they are screwed together), photocopy them for heavy use, and then screw them back together and store them in my library. I then have a photocopy to use, and the original does not get the wear.

[Guest 51Patty400]

I got mine on ebay for $75 from Autofax. I'll dig up the info and post it here tomorrow. It was a nice copy of the origianl I have (covered in oil, dogged eared etc)

- Mark

[Guest 51Patty400]

Here is the place I got mine from.

- Mark

[RO]

Here is a small database set up by a region of the SDC giving part number and source, although some of these may be "modern" part numbers and not directly cross reference to the originals. Kaiser and Hudson have similar databases set up, but Studebaker seems to be the most extensive set up with parts and parts sources of any of the orphans cars.

http://www.georgiastudebaker.com/interchange.htm