Compound Search

[andyamos79]

Hi all

I am a complete newbie to all things SQL and PHP so please forgive the ignorance. I found this website and have managed to setup a basic database on my Ubuntu server accordingly (great tutorial by the way). However, I am not sure whether or not my ultimate goal is feasible and if so how to achieve it.

The goal is to have a system that will allow for an easy search facility to locate all products that are assembled using similar constituent component parts.

For example, the basic structure would be that shown here:

Relationships.jpg (42.19 KiB) Viewed 4465 times

Each product can have many components and each has their own relevent details. What I need is the ability to return all products where all entered component details match. The issue here is I need to be able to specify multiple components and only return products where all these components match.

Example: (forgive the pseudo code style of writing)

search criteria : SELECT Products WHERE ComponentA.PartNumber = T AND ComponentB.PartNumber = U etc

There is no specific number of components per search and so, I may want to list all products where only ComponentA.PartNumber = T or Component[A - G].PartNumber = [T - Z]

The form would ideally start with a single PartNumber to search for and have an "add new search criteria" button which would simply append additional PartNumber to the query. The Description and the PartNumber are related in that you would only ever see a set number of PartNumbers for a given Description. In this case, Part Numbers from a brand of IC, say Siemens, will differ from Philips. I had looked into valuelists to provide a predefined selection of Descriptions and so when performing the query, the criteria would need to reflect this - (Description = DescriptionA AND PartNumber = PartNumberA)

I am completely aware that the structure of the database I have chosen may not be appropriate and so am open to suggestions as to how I should rearrange the stored data.

All components can exist on their own but several combined together will make up the product. Once all the data has been entered into the database for all Components, I should then be able to see what products have similar components fitted and thus source these products for their individual components in a bid to repair others.

I suppose you could look at this as a sort of catalogue for spare parts. The ProductID field within the Component table is used to relate an individual component to a specific product. The result should be a list of products, displaying the Serial Numbers of the Products and the Version for each component matched.

If anyone knows of a way to implement this and whether or not I am going about it the right way, I would be very grateful.

Andy

[sim]

Try filter = on http://xataface.com/wiki/filter
and see http://xataface.com/wiki/URL_Conventions

Hope this helps

[andyamos79]

Hi Sim

Thanks for the speedy reply. The links you gave are very useful indeed. I still a little unsure how to implement what I want.

Would you suggest using a filter and perhaps reorganising the database?

I suppose what is confusing me is whether or not I should be using separate tables for each class of components? For example, would it be easier for me to define a table for each of the main sections of the product? i.e. tables for Line Drivers, amplifiers, RF modulators, transformers etc? I guess in this way, I could specify a search somewhat easier by performing a search for LineDriver.Partnumber = A and Transformer.PartNumber = B etc and then have a static form that would allow you to input each value as necessary.

Would that be a better recognised means of arranging the data? I would have liked the type of component to be a valuelist based on a separate table. I could then add more types as time went on without having to alter the database extensively.

To be totally honest, I don't know enough about database design or implementation to know what I want but want to learn how to do this for myself rather than contract someone to do it for me - far more satisfying n'est pas?

Any more suggestions or ideas, please let me know.

Kind regards

Andy

[shannah]

This is a tricky search and there's no clean way to do this directly with SQL (off the top of my head). What I might do in this case is to create an "index" field in the products table that stores an encoded version of the component ids in the product. E.g. If the product contains component 1, 10, 67, and 201 this field would contain:
"-1-10-67-201-"
Ids need to be in ascending order here.

Then you could just do a search on the products table directly.
E.g.
All products containing component 3, 5 and 7:

Code: Select all

select * from products where component_index_field like "%-3-%-5-%-7-%"


This isn't a specifically Xataface solution but it's a start. Once you express a search in SQL it becomes much easier to create a UI to perform the search you want. (Xataface supports like queries by prefixing the value with "~".
E.g. in the component_index_field search field you would put:

Code: Select all

~%-3-%-5-%-7-%


This solution would require you to keep these index fields in sync with the database. You can either do this by periodically regenerating it, or updating it in the afterSave() trigger of component.

-Steve

[andyamos79]

Hi Steve

Thanks for the reply. Your suggestion makes sense. I will look into doing what you have suggested.

Thanks so much for your help.

Regards

Andy