Re: Clues on modeling a really simple concept

"Spike" <fauria_at_gmail.com> wrote in message news:28933dcc-983a-409f-b54e-4de451b46ffc_at_z15g2000yqm.googlegroups.com...
> On 6 abr, 23:11, Bernard Peek <b..._at_shrdlu.com> wrote:
>> In message
>> <16d72645-6a6b-4fd4-bccf-d5002970f..._at_q2g2000vbr.googlegroups.com>,
>> Spike <fau..._at_gmail.com> writes
>>
>>
>>
>> >My question is: Should i add a relationship between galleries and
>> >users?
>>
>> >I would like to query which galleries a user has, and i don't really
>> >know if it is worth adding a foreign key rather than doing a three-
>> >level join query each time i want to obtain this data.
>>
>> Less is more. The database is fully normalised without adding an extra
>> relationship. You shouldn't denormalise without a good reason.
>> Denormalised systems are usually more fragile and will cost you more
>> time in programming and maintenance.
>>
>> Of course if there is some other real-world relationship between
>> galleries and users you may need to change the model. Do galleries have
>> Owners, for instance and would those be Users?
>>
>> --
>> Bernard Peek
>
> Hi!
>
> I was thinking about system performance vs. normalization. By adding
> that extra relationship, three-level joins can be avoided, and so
> stressing the dbms too much.
>
> Do you think is a good idea in general to denormalize the data model
> by means of performance?
>
> Thanks!

I'm not sure what the term "three-level joins" means. It might mean the same thing as what I would call a "three way join".

Whenever you have a many-to-many relationship between two subjects in the database, and you compose data about the subjects in a query, you will have a three way join. Any DBMS worthy of your attention should be able to handle a three way join, or even a seven way join, without over stressing. Throughput will depend on the DBMS and its query opitmizer, but also on data volume, traffic on the database, available hardware, and physical design features, especially index design. Unless you have millions of pictures, you won't even have to sweat performance in this case.

In general, do not denormalize in order to improve performance. There are design disciplines that result in composed tables where normalization would have decomposed those tables. I'm a fan of one of those disciplines myself, but none of them are particularly worthwhile for the case at hand. And you should not attempt to learn any of those disciplines until you've mastered the craft of building normalized databases, at least to some minimal level.

You say that a picture can be in multiple galleries. And of course, a gallery can have multiple pictures in it. That's a many-to-many relationship. That means that you will need a junction table with two foreign keys in it, one to specify a picture and the other to specify a gallery.

As far as the relationship between users and pictures, or between users and galleries, that's a matter of the requirements. Even if you made a separate table for the "users own pictures" relationship, and yet another one for the "users own galleries" relationship, you could still compose all of these tables together with a seven way join. (The users table would be plugged in twice). Received on Tue Apr 07 2009 - 13:48:48 CEST