Trying to simplify db

I am a components engineer at an electronics manufacturer. Part of my job is to maintain a db of parameters for every part used in our products. I get these parameters from PartMiner, an online component search tool. My current db mirrors the PartMiner structure, but I keep running into two problems: 1) they change their attribute names from time to time, and 2) they do not use the same name across categories for seemingly common attributes (e.g.: tolerance or temperature). This makes it extremely hard to use flat tables for each category. I end up either adding columns or editing the column names to keep up with the changes.

My current db has a flat table for each PartMiner category of interest, a table of parts with their category ([Mfg Part Numbers]), and an [AllParts] table that has multiple Attribute/Value pairs for each component regardless of category. I have to search the [Mfg Part Numbers] table to find out what flat table to use to get the rest of the data. This will give me all the possible attributes for a given part, which is useful for comparing parts in the same category from different vendors, and determining all possible attributes for that category. The [AllParts] table makes it easier to get attribute/value pairs for any part without the use of a category.

Since there is always a possibility of adding a category (and the necessary table), this scheme becomes rather tedious. I have been looking into the TRANSFORM/PIVOT feature of MS SQL, and I have written some queries that create category "tables" from the [AllParts] table as needed. However, I am having problems filtering the results unless I actually make a temporary table from the transform, which becomes a problem for more than one user.

Here is my query:

Code:

---

TRANSFORM First(p.Value) AS FirstOfValue
SELECT p.VendorName, p.CleansedPN, First(p.Value) AS [Total Of Value]
FROM (SELECT  Status.VendorName AS VendorName, Status.*, AllParts.VendorName as PMName, AllParts.*
FROM Status INNER JOIN AllParts ON (Status.CleansedPN = AllParts.VendorPN) AND (Status.VendorName = AllParts.VendorName)
WHERE (((Status.Category)="Ceramic Chip Capacitor"))
ORDER BY Status.VendorName, Status.CleansedPN)  AS p
GROUP BY p.VendorName, p.CleansedPN
PIVOT p.Attribute

---

I want to wrap this in a SELECT statement with a WHERE clause something like this:

Code:

---

SELECT * FROM (the transform table) WHERE [Capacitance (Nominal) (uF)]='100p'

---

Any ideas?

The db is currently in MS Access, and I use ASP to display the results.

Thanks for your help!

As Chief architect of the PartMiner data systems, I can probably assist you. Some questions: 1) Are you an active customer of PartMiner? 2) If so, what is your license and access type? 3) Which databases are you licensed to be using? 4) Are you using any of our integration tools, XML server, or client toolkits or simply web page/HTML? 5) Are you propogating PartMiner data in your internal system(s)?

Thanks

Quote:

---

Originally Posted by PaulMag

As Chief architect of the PartMiner data systems, I can probably assist you.

---

Cool!

Quote:

---

Some questions: 1) Are you an active customer of PartMiner?

---

Yes.

Quote:

---

2) If so, what is your license and access type?

---

We have one seat of "Expert - All". Is that what you mean? We also have BOM Manager.

Quote:

---

3) Which databases are you licensed to be using?

---

Not sure I know the answer to that one.

Quote:

---

4) Are you using any of our integration tools, XML server, or client toolkits or simply web page/HTML?

---

web right now

Quote:

---

5) Are you propogating PartMiner data in your internal system(s)?

---

It is used by Omnify and DxDesigner for the engineers to select parts for their designs.

Quote:

---

1) they change their attribute names from time to time, and 2) they do not use the same name across categories for seemingly common attributes (e.g.: tolerance or temperature).

---

You need some kind of flexibility within the data and schema. I'm wondering whether encoding the various attributes to numbers linked to lookup tables would help. So perhaps...

TABLE 1:
attrib_uniq_id.

TABLE 2:
attrib_uniq_id. | attrib_name_link_id

TABLE 3:
attrib_name_link_id | attrib_name

So TABLE 1's attribute data could be
2
3

TABLE 2
2 | 4
3 | 5

TABLE 3
4 | Attribute name 1
4 | Attribute name 2
4 | Attribute name 3
5 | Attribute name 1
5 | Attribute name 2

It you are using MS Access it would mean some hijinx with ADODB and maybe an unbound form. You could subform it though.

Hope this is of some help. :)

I'm currently building a system where my company's parameters/criteria changes so often that my finance table is literally two columns: One for the amount and one for the financial type. I can add types whenever they need without having to add new columns on their whim. ;)

Hi TSQL Oz,

I think that is an extandable column definition strategy, but I'm wondering what do you do with the storage of the actual data.

Can you please add the structure that you see for storing the actual data of this extandable data schema.

In the end you have to store somewhere the relation between the data of each row, in a normal table that would be a normal row with values for each column, but in this structure I think you need some additional tables to help store the data, and then I wonder how can you select on those tables...

Regards,
jimmyy