MySQL Forums
Forum List  »  Newbie

Re: Opinions on this schema please =)
Posted by: Willem vanSchevikhoven
Date: February 22, 2009 08:41PM

Thanks again Rick for taking the time to give me some insight.

The number of the joined rows involved is really something i'll need to think about. Do you have any experience of using the where/on -statements twice to narrow down the results. Like:
------------------------------------------------------------
select * from

items join properties
on item.iid = properties.iid
and (
(properties.pname = 'property_1' and properties.pval = 'value1') OR
(properties.pname = 'property_2' and properties.pval = 'value1') OR
(properties.pname = 'property_3' and properties.pval = 'value1') OR)

JOIN I2A JOIN ARTICLES JOIN ARTICLE_PROPERTIES

# Do the proper where here
------------------------------------------------------------

An other thing that crossed my mind was to drop the final where and just have the AND / OR / NOT statements in the joins between items or articles and their properties. The only problem in this idea is that i dont have a clue how to implement it :P

Most likely i'd have an additional enum (item, article) on the properties, and then somehow hack down a search like "+Make:Nissan;+Model:200sx;+Type:Brakedisc;Diameter:280mm;" to two searches. One for the articles and one for the items. Then just join so i only get the pairs that are in the i2a -table.

I can do this with the OR's like above, but i think it's quite awkward to search for the same things twice :/

Could the mysql IF's be used to accomplish this?

PHP could ofcourse parse the search apart pretty quickly. I'd have to explode the search string in php anyways, so maybe making a quick query on a table with a couple of hundred rows and then using php to generate the two separate search strings wouldnt be that bad of an idea?
Still, making two queries instead of one is something that just doesnt feel right...



Edited 1 time(s). Last edit at 02/22/2009 08:42PM by Willem vanSchevikhoven.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.