So we've been running a Mysql dblib for a little while now and it's working great. This was a further step from the excel based dblib arrangement we had been using previously. I want to unlock the true power of a dblib based system now and connect the library database in with some simple stock management database and PHP scripting but in doing the planning I realise that my initial approach with the dblib table layout is probably not ideal for this.
The current arrangement is a single database with separate tables for each component category:
- ceramic_capacitors
- electrolytic_capacitors
- resistors
- discretes
- integrated_circuits
etc.....
Obviously when trying to link this in with other databases and functions this all should be in a single table with an additional column used to indicate the component type (really just an integer linked to a foreign key in a table with the description text). My concern is how do we then easily select the component categories in Altium? At current the separate tables each show as a separate list in the library pane in Altium, which is great. Using a single table all of the components will be in one giant list. Yes, we could filter on the component type column, but as this is a foreign key I don't believe that it's possible to get Altium to do a table join so the component type would simply be a meaningless integer.
The only other option I was considering would be to define each component type in a different dblib file. There is an option to include a WHERE clause in the dblib file (so could speocify the component type integer in there) but I believe this is only for specifying the primary key for Altium to link the component with.
Does anybody have a suggestion for how best to configure this?