Hi all, first post here, hope you guys can offer some advice.
I’m looking for help with a design concept that I just can’t get my head around at the moment to control and document the components we use in building circuit board assemblies.
At present we use a spreadsheet that is updated and saved with a new revision ID in its file name each time a new component is (or multiple components) are added or a change made to existing component(s)
Currently this is around 1600 components, each with multiple approved manufacturers.
To create a snapshot of this information, I would plan to use something like;
... other information specific to designed part - tolerance, size etc.
' would list multiple manufacturers and their part numbers for each ID part
What I’m struggling to get my head around is how to create the third dimension provided by the spreadsheet file name – essentially, how to create an revision history.
The current approach for this data is that any change to any of the data results in a new revision of the document. So a new component added, an old part obsoleted, an error in a manufacturer or part number corrected will result in 'components rev 123.xls' being updated and saved as 'components rev124.xls'
I can add a revision field and every time a component is added / changed, the whole data gets copied with a new revision ID, which to me is a quick and dirty (and unsustainable) way to make it work
How should I structure the tables and then what would the format of a query be to retrieve a particular revision of the data.
Really having a mental blank.
I think the most-practical way is to include the revision-level in the table name and then copy and edit the table to conform to the new revision. But that's no different than what you're now doing with a spreadsheet.
Another approach would be to add a rev-level field to your table and update each record (component) that is included in the new revision to the new rev level but then you'd have no record of previous revisions. If you place the components in fields and rev-levels in records, your problem is finding a database that can have 1600 fields.