Re: Opinions on this schema please =)
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.
Subject
Written By
Posted
February 19, 2009 03:34PM
February 19, 2009 05:53PM
February 21, 2009 12:07AM
February 21, 2009 07:59AM
February 21, 2009 10:47AM
February 21, 2009 05:40PM
February 22, 2009 01:36PM
February 22, 2009 02:13PM
Re: Opinions on this schema please =)
February 22, 2009 08:41PM
February 23, 2009 09:13AM
February 23, 2009 09:36AM
February 23, 2009 04:27PM
February 26, 2009 05:15PM
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.