3

I am an electrical engineer developing a parts library database and explorer tool for internal use in my organization. If anyone is familiar with OrCAD schematic capture tools, I am essentially recreating Cadence's CIS (Component Information System) backend and CIP (Component Information Portal) frontend , but I attempting to make them CAD software agnostic (should work with OrCAD, Altium, KiCAD, etc). I am nearly at the finish line, but am getting tripped up with the database views I need to feed data into my CAD software

At the moment I have 6 tables that all contain different types of data about each part:

Table Name Purpose Relationships
classes Part categories Parent to Main
main Descriptive identifying information and data for CAD tools Child of classes, Many-to-One. Parent to all other tables. Has Internal Part Number (IPN) used as foreign key in the other tables.
performance_spec Part ratings used for automated part stress derating analysis. Part class determines criteria. Child of main, One-to-One
manufacturing Manufactures and Part Numbers Child of main, Many-to-One
procurement Suppliers and Part Numbers Child of main, Many-to-One
compatibility Cross reference alternative parts - Same Form, Fit, and Function with small differences in finish, reliability rating, etc. Child of main, Many-to-One

The main issue is that the CAD software, which communicates with the database via ODBC, cannot handle data split into multiple tables. So I need to create views (one per library -- grouped by class) that display queries of joined data from each of the tables.

The "Many-to-One" relationships between records is posing a particular problem because I need to represent all child records in the manufacturing table in a single row with the parent records in the main table.

I currently have a view that pulls in all of the child records using LEFT JOINs, but each child record in the manufacturing table creates a new row. I need some way to query the data so all of the child records are in a single row.

Here are simplified structures of the main and manufacturing tables along with some sample data.

main

id ipn part_class part_value description package spec_doc sch_symbol_kicad pwb_fotprint_kicad last_modified_user last_modified_timestamp
1 PRT-00000001 RES, Other 10k RES, SMT, Thick Film, 10k, 1%, 100ppm, 0.15 W 0805 res resc0805 FLast 2023-05-04 07:38:53.782386-04
2 PRT-00000002 CAP, Tantalum, Solid 15u CAP, SMT, Tantalum, 220u, 10%, 10V, Reliability Grade D, Surge Test Option C 2214 MIL-PRF-55365/11 cap_p capc2214 FLast 2023-05-03 07:31:05.815801-04

manufacturing

id ipn mfr mpn
1 PRT-00000001 Yageo RC0805JR-0710KL
2 PRT-00000001 Stackpole Electronics Inc RNCP0805FTD10K0
3 PRT-00000001 Susumu RG2012P-103-B-T5
4 PRT-00000002 Kemet T429F156K020DC4252
5 PRT-00000002 Kyocera AVX CWR29JC156KDFC

I'd like to create a view that looks like this:

ipn part_class part_value description package spec_doc sch_symbol_kicad pwb_fotprint_kicad mfr_1 mpn_1 mfr_2 mpn_2 ... mfr_n mpn_n

If someone could help me figure out a query to structure the manufacturing table with all records corresponding to the same IPN in a single row that would be a huge help. Joining that with the main table or any others should be straight-forward.

I have looked into pivoting and cross-tab functions, but every example I've seen has used aggregations on numerical data, which doesn't directly apply to me.

Thanks in advance!

Edit-001 Added SQL code to recreate tables:

CREATE TABLE components.main (
    id integer NOT NULL,
    ipn components.citext DEFAULT to_char(nextval('components.main_ipn_seq'::regclass), '"PRT-"fm00000000'::text) NOT NULL,
    part_class integer NOT NULL,
    part_value components.citext NOT NULL,
    description components.citext NOT NULL,
    spec_doc components.citext,
    sch_symbol_kicad components.citext,
    sch_symbol_altium components.citext,
    pwb_footprint_kicad components.citext,
    pwb_footprint_altium components.citext,
    step_file components.citext,
    lead_frm_reqd boolean DEFAULT false NOT NULL,
    lead_finish components.citext,
    datasheet components.citext,
    last_modified_user components.citext DEFAULT USER NOT NULL,
    last_modified_timestamp timestamp with time zone DEFAULT now() NOT NULL,
    nrnd boolean DEFAULT false NOT NULL,
    bom_exclusion integer DEFAULT 0 NOT NULL,
    pwb_exclusion integer DEFAULT 0 NOT NULL,
    package components.citext
);

ALTER TABLE components.main ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME components.main_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE SEQUENCE components.main_ipn_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE components.manufacturing (
    id integer NOT NULL,
    ipn components.citext NOT NULL,
    mfr components.citext NOT NULL,
    mpn components.citext NOT NULL,
    cage components.citext
);

ALTER TABLE components.manufacturing ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME components.manufacturing_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

Edit-002 I figured out how to get the view to display what I wanted using hierarchical queries, but it's pretty slow (~100-200ms for ~200 records). Any recommendations for performance optimization strategies would be greatly appreciated!

Here's my code:

CREATE OR REPLACE VIEW components.manf_pair_index
AS
SELECT manufacturing.id,
    manufacturing.ipn,
    manufacturing.mfr,
    manufacturing.mpn,
    row_number() OVER (PARTITION BY manufacturing.ipn ORDER BY manufacturing.id, manufacturing.ipn) AS pair_num
    FROM manufacturing;

CREATE OR REPLACE VIEW components.manf_flat
AS
SELECT manf_pair_index.ipn,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 1) AS mfr_1,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 1) AS mpn_1,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 2) AS mfr_2,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 2) AS mpn_2,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 3) AS mfr_3,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 3) AS mpn_3,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 4) AS mfr_4,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 4) AS mpn_4,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 5) AS mfr_5,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 5) AS mpn_5
    FROM manf_pair_index
    GROUP BY manf_pair_index.ipn;

CREATE OR REPLACE VIEW components.resistors
AS
SELECT main.ipn,
    main.nrnd,
    main.part_class,
    main.part_value,
    main.description,
    main.package,
    main.spec_doc,
    main.lead_finish,
    main.lead_frm_reqd,
    manf_flat.mfr_1,
    manf_flat.mpn_1,
    manf_flat.mfr_2,
    manf_flat.mpn_2,
    manf_flat.mfr_3,
    manf_flat.mpn_3,
    manf_flat.mfr_4,
    manf_flat.mpn_4,
    manf_flat.mfr_5,
    manf_flat.mpn_5,
    compatibility.comperable_ipn,
    main.sch_symbol_kicad,
    main.sch_symbol_altium,
    main.pwb_footprint_kicad,
    main.pwb_footprint_altium,
    main.step_file,
    main.bom_exclusion,
    main.pwb_exclusion
    FROM main
        LEFT JOIN manf_flat ON main.ipn::text = manf_flat.ipn::text
        LEFT JOIN compatibility ON main.ipn::text = compatibility.ipn::text
    WHERE main.part_class > 28 AND main.part_class < 45
    ORDER BY main.ipn;
Improve this question

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.