Re: Re: Dumb Database schema / EOModeler questions...

This was amazingly helpful.  Thanks a lot!  I was initially worried
that calling aUser.friends().stories() would be very inefficient,
however I suppose the "WebObjects Way" is for me to trust WO/EO to
take care of it for me... Seemed too easy to be true, I suppose.

-numair

On 11/1/06, Jerry W. Walker <[email protected]> wrote:
> Hi, Numair,
>
> On Oct 22, 2006, at 11:20 AM, Numair Faraz wrote:
> > I'm working on a "social news" application written in WO, and am
> > currently in the process of modeling my database (we're going to use
> > FrontBase).  As I am not an expert at database modeling / creating
> > scalable solutions - embarrassingly bad, to tell you the truth - I
> > wanted to get some perspectives from the people on the list regarding
> > what they'd do.  My proposed implementations seem awfully simplistic,
> > which leads me to believe that they won't scale ...
> >
> > Basically, we have three tables: USER, FRIEND, STORY; what I want to
> > figure out is...
>
> First, without regard to efficiency, I think you've described a two
> entity situation with USER and STORY as your entities.
>
> > - What is the most efficient way to set up the FRIEND table?  Assuming
> > that when a user first asks to be a friend of another user, the
> > connection remains pending until the other user confirms (thus meaning
> > all USER.friends relationships are bi-directional), should I have
> > things set up so that FRIEND is as such:
> >
> > FRIEND
> > ======
> > friendId - LONGINT, Primary Key
> > userId - LONGINT, Foreign Key
> > user2Id - LONGINT, Foreign Key
> > approved - BOOLEAN
> >
> > owner - relationship to USER: FRIEND.userId + USER.userId
> > friend - relationship to USER: FRIEND.user2Id + USER.userId
> >
> > .. with an approval by the other user meaning that another FRIEND
> > entry for their relationship?  I am assuming that two transactions /
> > table entries are very inefficient, and that this will cause problems
> > for me later on, but that's the best solution I could think of.
>
> The friends relationship should be a many-to-many relationship from
> USER to USER. This is handled in EOF by a join table which has a
> compound primary key (PK) comprising two foreign keys (FKs), one
> pointing to each of the two friends. EOModeler can establish that
> table for you and will call it USER_USER in your case. If you follow
> the rest of my advice, I would rename this table to something like
> FRIENDS before doing the next step.
>
> Understand, however, that a join table should not be reflected in
> your code with a corresponding class. Join tables are merely an
> artifact of relational databases that establish many-to-many
> relationships there. These many-to-many relationships are reflected
> in your WO code as arrays belonging to each of the related entities
> whose elements point to objects of the other entity in the
> relationship. In EOModeler, where the entities that represent classes
> in your code will have an entity name, a table name and a class name,
> join tables will have an entity name, typically formed as a
> concatenation of the two entities it's joining, a table name
> reflecting the entity name in the format used for tables in your DB,
> and a class name of EOGenericRecord.
>
> I would also set up a second many-to-many reflexive relationship on
> USER called potentialFriends for one USER and requestedFriends for
> the other. When a requestedFriends confirms the relationship, it is
> removed from the potentialFriends/requestedFriends relationship and
> is re-established in the friends relationship. No boolean is
> necessary and actual status of the friendship is still clearly
> indicated and quite accessible.
>
> This second (potentialFriends/requestedFriends) relationship would be
> physically implemented in the database with another join table whose
> PK again comprises two FKs, one pointing to the requestingFriend and
> one pointing to the potentialFriend. Once again, EOModeler can
> establish the table for you and would call it USER_USER as well,
> which is why you need to rename the first User_User table before
> doing this step.
>
> I would strongly suggest reading through the EOModeler Help
> documentation before doing all of this. It is easily available from
> EOModeler's Help menu.
>
> > Now, assuming that we have USERs and USER.friends .. Say we have the
> > following STORY table:
> >
> > STORY
> > ======
> > storyId - LONGINT, Primary Key
> > userId - LONGINT, Foreign Key
> > body - CLOB
> > approvedByEditor - BOOLEAN
> >
> > author - relationship to USER: STORY.userId + USER.userId
>
> In Entity-Relationship modeling terms, I would change userId to
> authorId in STORY and use it to establish a one-to-many relationship
> between USER and STORY called stories from the USER side and author
> from the STORY side.
>
> > .. How would one fetch all of the stories posted by the friends of a
> > user in the most efficient manner?
>
> Let EOF do the fetching by obtaining aUser and asking (in your WO
> object code) for aUser.friends().stories(). EOF will generate the SQL
> for you. You can increase the efficiency of the fetch by setting a
> reasonable batch size in the "Batch Faulting" field of the Advanced
> Relationship Inspector of EOModeler. However, I would presume that
> you would include some sort of NSTimestamp storiesReadToDate field
> for the User and NSTimestamp writtenDate so you would only fetch
> stories from that User's friends whose writtenDate was later than the
> User's storiesReadToDate.
>
> > My avoidance of reading a proper
> > database modeling book has caused me to be incapable of even beginning
> > to figure this out, let alone figure out how I would model it in
> > EOModeler (blame the US education system...).
>
> Blame yourself. If you're a professional in this field, building apps
> that rely on databases, then you should pick up a good book on ER
> modeling and study it. I'm not sure what the best book is today, but
> when I was studying this area, Chris Date's book,
> _An_Introduction_To_Database_Systems_ was the gold standard.
>
> > I also need to figure
> > out how to fetch an array that contains both a user's friend's
> > stories, and stories that are marked approvedByEditor, with no
> > duplication; again, no clue how I would do this in EOModeler (which I
> > hadn't used in about 4 years, until this week)...
>
> I wouldn't try to do this in EOModeler. I would do it in my WO code
> as indicated before by asking aUser for aUser.friends().stories() and
> then filtering the result with approvedByEditor(). After having done
> this, you might do some load testing to determine where the
> bottlenecks in your application are. If you find that this request is
> one of the bottlenecks, you might fashion a fetchSpecification using
> an appropriate EOQualifier for it. But I wouldn't do that until I
> knew that this was a bottleneck in a working system. My long
> experience with systems is that appropriate optimization can often be
> counter-intuitive and optimization without profiling often pays a
> high maintenance cost for no optimizing advantage.
>
> > Hope you guys can help ...
> >
> > Numair
>
> Hope this helped.
>
> Regards,
> Jerry
>
>
>
> --
> __ Jerry W. Walker,
>     WebObjects Developer/Instructor for High Performance Industrial
> Strength Internet Enabled Systems
>
>      [email protected]
>      203 278-4085        office
>
>
>
>