Stew,
Good points about databases. I made most of my career off of relation databases and systems design and development. In this case because the data set is small, it is not a huge problem if there are some flat aspects to it, but it does make it difficult when you want to make changes. One thing I would suggest, is not to have a table (database) that is manufacturer / distributor specific, but to include their information in a separate table with a reference to "vendor number" or "source". Then when a new source becomes available (lke Steve Pierce emptying out his hangars of treasure) they can simply be added to the source database, and that code placed in the source lookup table for that specific part number.
I have converted many many systems from Access to other platforms, recently, mostly some brand of SQL. Access is a great prototyping tool and I know tons of organizations still using it everyday to run their businesses. - in spite of its antiquated interface and inability to interact well with the on-line world.
One question I have, is how prevalent are alternative part numbers? If so, it would be good to have a part cross reference table.
I see a structure like this:
Master Part Reference (description, master part number, link to image of part, STC 337 info, etc) Master Part number is the primary key on record per part number
- Cross reference (master part number, alternative part number) - multiple records per part.
- Models (master part number, applicable aircraft models this part works for by serial number, etc) - multiple records per part
- Suppliers Parts (master part number, supplier part number, supplier number) - multiple per part
- Supplier Master (supplier number, contact information for suppliers, blah blah blah) one record per supplier
- Notes (master part number, user notes and links on using parts)
That's off the top of my head after one cup of coffee.... I'm sure I missed something!
sj