Re: Dumb Database schema / EOModeler questions...

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