On Wed, 2010-06-16 at 15:56 +0200, Carlos Medina wrote: > Am 16.06.2010 15:50, schrieb Jan Reiter: > > Richard, Carlos, Simcha and Nigel, > > > > > > Thank you all for your answers on this matter. When I read Simcha's mail, it > > hit me like a hammer! > > That was what I was looking for! BUT: I have to see, that this creates way > > to much overhead, as I have to use INNER JOIN instead of LEFT JOIN, or an > > equvalent subquery. > > > > I'm currently testing with temporary tables to filter the data before going > > into the real query. > > > > As this is not a real project, just me testing the pros and cons of speed > > versus flexibility of exactly this table structure, there is no need to > > restructure the tables. > > > > I use 30,000 images as sample data, 7 attributes each. > > > > What I have for the images is 3 tables. > > > > table picture > > pid(primary): the id of the picture > > uid(index): the user id, to whom the image belongs > > > > > > table picture_attribute > > aid(primary): the id of the attribute > > name(index): a name for that attribute e.g. fsize > > > > > > table picture_attrib_rel (unique over pid,aid - to avoid the same attribute > > twice > > pid(index): the picture, this row belongs to > > aid(index): the type of attribute this row describes > > val_int(NULL): a field for integer values | > > val_vc(NULL): a field for string values | my class decides > > wich of these value types to use, > > the other is NULL > > > > My class emulates a foreign_key behavior on aid between picture_attribute > > and picture_attrib_rel. > > > > > > THANK YOU ALL!! > > > > Regards, > > > > Jan > > > Hi Jan, > pherhaps because i am working in a projekt with pictures too, but the > way i found to solve this was over the same as you are doing now. I took > a nm relation with attributes of picture configurations (configuration > means width, height, print_material, ink and so one) and add it to the > relation on a temp table (only for the search). > > Regards > > Carlos > Hi Jan, Just thought of something else you could do to avoid data corruption. At the moment you're using integer values to describe the attribute type. Whilst this may be fine for your code, I've often found that people like to edit values in the database directly because it's faster, and you could end up with issue if they put the wrong values in the wrong places. I believe MSSQL (which is what I think you said you were using) has caught up now and has an enum field type which would allow you to use string values in your queries, but internally they are still stored as a very small integer (tinyint or the mssql equivalent iirc) which will save on storage space. I often do this for database fields (in MySQL) where I know a field will only ever have a limited range of values, as it sure helps. This is unless of course the integer value is actually the index of another table indicating the type, in which case ignore what I said! Thanks, Ash http://www.ashleysheridan.co.uk
