Depends, but I think there would always be a difference in favour of
normalized data because the data access is fully indexed. However, this
difference may be almost negligible if your main table is less than say 10K
records and your queries are simple.
In my experience, where performance differences are slight, it is almost
always better to go with the easiest coding -- it is better to get the job
done than study the latest indexing algorithms!
You should consider how complex your queries will be, and it is probably
advisable to do your own tests on your chosen platform.
One other advantage of denormalized data is that by avoiding joins it makes
it easier to use NoSql databases, if in the future you want to go that way.
David
On Sunday, 15 April 2012 13:16:31 UTC+1, leftcase wrote:
>
> Heh, no worries. :-)
>
> I wonder, just out of interest, is there any great performance difference
> between searching for records containing region.id 'x' (as below) rather
> than returning all rows with region.id 'x' from a junction table?
>
> Chris
>
> On Sat, Apr 14, 2012 at 11:34 PM, villas wrote:
>
>> Oops I think I got confused with the vertical bars, try it with just a
>> plain integer:
>> houses = db(db.house_types.regions.**contains(7)).select()
>>
>>
>> On Saturday, 14 April 2012 22:33:54 UTC+1, leftcase wrote:
>>
>>> Thank you again David,
>>>
>>> I shall give it a go.
>>>
>>> Chris
>>>
>>>
>>> On Sat, Apr 14, 2012 at 10:29 PM, villas wrote:
>>>
>>>> Yes, it would store the ids of the regions between vertical bars,
>>>> something like this: |4|7|23|. So say you are looking for the house-types
>>>> in China (which is id = 7). It would be something like this:
>>>>
>>>> if you already have the record id no.7, then just this:
>>>> houses = db(db.house_types.regions.**contains('|7|')).select()
>>>>
>>>> Or, you might have the find the record no. first:
>>>> china_rec = db(db.regions.name == 'China').select(db.regions.id)**
>>>> .first()
>>>> houses = db(db.house_types.regions.**contains('|'+str(china_rec.id)**
>>>> +'|')).select()
>>>>
>>>> As long as your requirements are not too complex it seems to work well.
>>>> See also the book. DAL chapter, search for list:reference.
>>>>
>>>> Regards, D
>>>>
>>>> On Saturday, 14 April 2012 21:27:34 UTC+1, leftcase wrote:
>>>>
>>>>> Hi David,
>>>>>
>>>>> Thanks for your reply.
>>>>>
>>>>> I did come across the list:reference option while trying to figure
>>>>> this out. I couldn't figure this out though:
>>>>>
>>>>> Say I add the following to my house-type table:
>>>>>
>>>>> Field('regions', 'list:reference region')
>>>>>
>>>>> If I generate a form using SQLFORM, it presents me with a multiselect
>>>>> region option where I can select as many regions as I need. Reading
>>>>> around,
>>>>> it seems that the region field would then store something like this for
>>>>> instance:
>>>>>
>>>>> 'england', 'france', 'US', 'china'
>>>>>
>>>>> How do I then create a query to show all house-type records with a
>>>>> region of 'china' for instance?
>>>>>
>>>>> I'm not an experienced user of development frameworks like this. Is it
>>>>> usually this difficult to accomplish this kind of thing? Seems to me it
>>>>> would be a pretty common requirement?
>>>>>
>>>>> Thanks in advance, :-)
>>>>>
>>>>> Chris
>>>>>
>>>>> On Sat, Apr 14, 2012 at 8:52 PM, villas wrote:
>>>>>
>>>>>> I would just mention that if you only have a few regions/housetypes,
>>>>>> you might consider de-normalising the data and using list:reference and
>>>>>> work with the jQuery.multiselect (if you have any problems with that,
>>>>>> you
>>>>>> might like to read my other thread on the topic).
>>>>>> Best regards, David
>>>>>>
>>>>>> On Saturday, 14 April 2012 18:31:58 UTC+1, leftcase wrote:
>>>>>>>
>>>>>>> Hi all,
>>>>>>>
>>>>>>> I'm pretty new to web2py and web app development and I've spend some
>>>>>>> time trying to figure out the best way to do this.
>>>>>>>
>>>>>>> I have two tables, house-types and regions. A house-type can exist
>>>>>>> in many regions, and a region can have many house types:
>>>>>>>
>>>>>>> db.define_table(
>>>>>>> 'region',
>>>>>>> Field('name', 'string', length=512, required=True),
>>>>>>> format = '%(name)s')
>>>>>>>
>>>>>>> db.define_table(
>>>>>>> 'house-type',
>>>>>>> Field('title', 'string', length=512), #title of the newbuild
>>>>>>> property listing
>>>>>>> Field('vendor',db.vendor, required=True),
>>>>>>> Field('bedrooms', 'integer'),
>>>>>>> Field('price', 'double',required=True),
>>>>>>> Field('description', 'text',length=65536, required=True),
>>>>>>> Field('live', 'boolean', default=False)
>>>>>>> )
>>>>>>>
>>>>>>> If I understand correctly, in order to create the many-to-many
>>>>>>> relationship I should create a junction table like the following:
>>>>>>>
>>>>>>> db.define_table(
>>>>>>> 'houses_and_regions',
>>>>>>> Field('house', db.house-type),
>>>>>>> Field('region', db.region))
>>>>>>>
>>>>>>> And then I should use SQLFORM to construct a form which updates both
>>>>>>> house-type and houses_and_region tables when I create or modify a new
>>>>>>> house.
>>>>>>>
>>>>>>> What I'm struggling with is how to create a form which allows me to
>>>>>>> select multiple regions for a house. I wondered if anyone could give me
>>>>>>> any
>>>>>>> examples?
>>>>>>>
>>>>>>> Thanks in advance!
>>>>>>>
>>>>>>> Chris
>>>>>>>
>>>>>>
>>>>>
>>>
>
