0

I was searching for an application to manage (electrical) components that complies with the following criteria:
- open source
- has a purchase-history
- allows filtering for type-specific attributes (abstract example: two categories A, B exist; following attributes belong to them: A.a, B.b, A.c, B.c; when choosing one category it should be possible to filter for those category specific attributes a or b)
- must: linux, optional platform-independant

Here is an extraordinary example: http://uk.farnell.com/
And with a sample-search: filters applied

I didn't find a program that meets the criteria and so tried to build a webapplication myself.The problems arose when trying to find a database-layout.

Here some thoughts:
-> use a table for each category, subcategory, sub-subcategory, ... -> the most specific subcategory for a part has a reference to this part (that is in a part table)

I could not figure out if/how it is possible then to dynamically add new categories without changing all the SQL queries. My idea was then to have a name-schema that allows me to write some code that analyzes those names and creates the query on the fly but that feels quite hacky.

Maybe someone has a reference to a database example or some tips? (further information can be found in the edit-history, i deleted it because it blows up the post too much)

michi099
  • 49
  • 2
  • 9
  • This is very off-topic. It is either a request for software or it is a question so broad that answers would be book-length. (Also, a quick google search shows that there are readily available starting points. E.g.: https://partkeepr.org/) – Mitch Aug 09 '17 at 14:03
  • And if this is exclusively the question "how do I store attributes in a database?", then it is a duplicate. See [How to design a database for User Defined Fields?](https://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields) – Mitch Aug 09 '17 at 14:13
  • first of all sorry for being offtopic - would it be the best to delete the question? thanks for your answers anyway! partkeepr has the huge problem, that it can't search for a parameter of a specific category (e.g. category mosfet, current > 5A) or list by parameter in a specific category. furthermore parameters must be added again for a new part but in my point of view the parameters should be already defined for a specific category. the question for me is now if it would be easier to change the project or start something new. i would be satisfied with a simpler graphical representation too – michi099 Aug 10 '17 at 12:53
  • I'm not sure on delete etiquette. You may want to check the [Help Center](https://stackoverflow.com/help). W/r/t PartKeepr, I think it is meant to pull part information from Octopart. – Mitch Aug 10 '17 at 14:23
  • thanks for your help again your link above was quite helpful! trying not to be rude I would be very thankful if you could provide search terms/hints what to look for by trying to create a database-layout. Then only methods I can think of: a) one table that contains all possible categories, another one that contains all available attributes, a n:m mapping table that maps the attributes to the categories and then a table with many columns that contains all the categories as column names. for select/insert query the needed attributes for given part, set others to NULL. – michi099 Aug 11 '17 at 08:23
  • b) a table for every category, a table for all parts - the most specific subcategory for a given part then references the part. creating a new part involves inserting values to the most specific and all parent tables. that allows searching/filtering at every category-level. What is very unconvincing here is that I didn't find a way to specify generic queries. Is it necessary to specify a query for each category here? An example: one could choose __n-channel mosfets__ one could choose __mosfets__ but one could also choose __transistors__ and so on... or are ther further options? – michi099 Aug 11 '17 at 08:31
  • Usually you would take advantage of the specific functionality of your database server. For example, in MS SQL (and several others) I would implement this as either a sparse table or with indexed XML. If the number of categories is low, I would consider the "one table per type" model, but not if queries have to traverse multiple tables. In both cases, you could have a very high number of attributes while still being able to create indexes as needed. There are a number of NoSQL databases which also handle schemaless queries that could be of use. Any of the terms above should get you close. – Mitch Aug 11 '17 at 14:50
  • Also, you can just [buy the functionality](https://learn.microsoft.com/en-us/azure/search/search-faceted-navigation), or use a DB which has built in support for attributes and faceted search. ([Solr comes to mind.](https://lucene.apache.org/solr/features.html)) – Mitch Aug 11 '17 at 15:04

0 Answers0