Hi everybody!

I'm trying to model a photo gallery, and i have a doubt on how to
implement it.

I have a table for users. I have another table for pictures. And
finally, i have a table for galleries (which are sets of pictures).

Both galleries and pictures belongs to a single user, but a single
picture can be used on many galleries.

So, i have something like this:

users --> pictures --> pictures_to_galleries --> galleries

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.

Thank you very much!

On Apr 6, 1:01*pm, Spike <fau... (AT) gmail (DOT) com> wrote:
Based on what you've written, I think I'd model this as U -->> P
(1:M) , U -->> G (1:M), and P <<-->> G (M:N), with the latter resolved
as P -->> PG <<-- G. So, yes--you could get at the galleries through
the pictures, but what you're ultimately saying is that users create
galleries to assign pictures that they own to.

--Jeff

In message
<16d72645-6a6b-4fd4-bccf-d5002970f9ff (AT) q2g2000vbr (DOT) googlegroups.com>,
Spike <fauria (AT) gmail (DOT) com> writes
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

Spike wrote:

Design by usenet is a futile endeavour. You really do have to look at
all of your requirements, and it's not really feasible to communicate
them via usenet.

That said:

The only similar application I am familiar with is istockphoto.com

In that application, the owner of a picture does not necessarily own the
galleries it appears in. One can create and populate a gallery owning no
pictures at all. This suggests to me that galleries have some
association with users completely separate and independent from pictures.

But that's just one application and not necessarily your application.

On 6 abr, 23:11, Bernard Peek <b... (AT) shrdlu (DOT) com> wrote:
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!

On Apr 6, 3:40*pm, Spike <fau... (AT) gmail (DOT) com> wrote:
Spike,

I'm not sure if you're deliberately trying to be provocative, but I'd
say it's fair to characterize this newsgroup as denormalization-
hostile ;-).

What's more important, I think, is to put first things first, and as I
hinted at--and others made explicit--what's paramount is getting the
requirements right and accurately modeling the problem domain. If
users create galleries, and/or galleries can exist independently of
any pictures being assigned to them, then model accordingly. Period.

Bottom-line, IMHO: 1) model the domain accurately, and 2) deviate from
the model only when *truly* warranted (NB that often denormalization
is zero-sum--who cares when the data comes back faster when it's wrong
or contradictory?). You might want to see if you can find a copy of
"The Normal Is So... Interesting" by Chris Date (DBP&D 11/97) for more
on the topic of denormalization. Many other sources abound.

Regards,

--Jeff

On 7 abr, 01:38, jefftyzzer <jefftyz... (AT) sbcglobal (DOT) net> wrote:
Its the very first time i post (and even read) this newsgroup, so no,
im not deliberately provocative.

The problem here is that users should be able to upload pictures
without specifying a particular gallery (i will use a default one).
In a similar way, the should be able to create an empty gallery.

Thus, there are two options: make a relationship between users and
galleries or create a record in the join table between pictures and
galleries with null values on foreign keys.

I dont really like any of the two options, but if one of them
increases overall performance, that will be a good decision maker.

Thanks!

Spike wrote:

The latter is wrong. Wrong in so many ways and on so many levels, it
would be tedious to try to enumerate them.

If a user can create and own empty galleries, then, obviously, gallery
has some direct join path to user. Failure to capture that would lead to
stupid errors like the inclusion of another person's picture in one's
gallery suddenly conferring ownership of the gallery to the other
person. That, in itself, is an obvious potential security flaw.

I strongly recommend you learn at least some of the fundamentals before
you start designing anything that might impact the public.


Like and dislike has nothing to do with acting like a professional.
While performance is an important consideration, fast and incorrect is
no better than slow and incorrect; it is simply incorrect.

A responsible and competent designer starts with correct and works from
there to achieve the required performance characteristics. Only an
ignorant boob would design for performance without any consideration for
the actual requirements.

"Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote

I have to agree with Bob on this point.

In message
<28933dcc-983a-409f-b54e-4de451b46ffc (AT) z15g2000yqm (DOT) googlegroups.com>,
Spike <fauria (AT) gmail (DOT) com> writes


If you need better performance then it's one way of getting it, but any
move away from a fully normalised structure will cost you more time and
effort in building the application and managing the data. In some
situations the right answer is to denormalise. In other situations the
right answer is to upgrade the hardware. In some other situations you
might need to do both. But if you build the system using a fully
normalised structure and users aren't complaining about performance then
you don't need to do anything.

I suggest building the system using a fully normalised structure. Try
that out on the users and see whether there are complaints about
performance.

If users complain then you need to make some decisions. I can't make
those decisions for you without a thorough analysis of your particular
application, which I'm not going to attempt via Usenet. If you want me
to do a thorough analysis we need to talk about my rates for
consultancy.



--
Bernard Peek