Dblib Table Layout

[Mangozac]

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?

[ajb]

Why do you want different tables for different component types? If it's because you're putting a whole shitload of type-specific parameters into the database, I would advise against that.  You're making it harder to maintain, and you're never going to get enough data in there to replace the datasheet  Even if you transcribe all of the parameters you also need to include measurement conditions to be able to understand how they apply, and often you need to look at curves or tables, so it's just not worth the effort.  Just put the headline specs into the description field and throw a link to the datasheet in there. 

I used to have separate tables for different types, but I switched to a single table.  I have fields for component type and then three levels of subtypes that are set for grouping in the AD component browser, so it's super easy to drill down to the right thing.

There have been a few other threads on this topic, here's one that has a link to a couple others (and a screenshot showing the sort of hierarchy I have): https://www.eevblog.com/forum/altium/dblib-with-individual-tables-for-each-component-type-thoughts/msg2730380/

[Mangozac]

Why do you want different tables for different component types?

I don't - we only have that now as a holdover from the previous excel based dblib. It was a useful way to categorise the components and it's a fairly minimal set of parametrics in each table. What I'm after now is a way to have similar, easy to use categorisation in the Altium part selection panel but having all of the components in a single database table.

What you describe is what I want to do, but is there a way to do this without each table row having to have the actual category text in it? It would be more appropriate to have an integer that links to a foreign key descriptive text but I don't think Altium is able to do that kind of link.

[Mangozac]

I discovered this morning how this is done - not in Altium Designer but in a MySQL feature called "views". As a self-taught MySQL user I had never heard of this feature before but it is quite powerful. Essentially you create a View, which is an SQL query that is given a name like a table but can include all of the conditions (such as selecting components by category). So I will have one large table with all components but then also a query which links in the foreign key to get the description from the "categories" table.

[lecatron07]

while this is all about database and altium i have a question and a problem

1. im using a server type database the database itself is good but when i want to add custom library to my project altium start to be very slow
  when i try to change foot print of any component altium get stuck for at least  5 minutes  any idea why

2. does altium support PostgreSQL