Re: sql (Stored procedure) design question

Alban and Tino,
 More explanations lie below.
A document contains to field, from field, subject, message, etc

> >>documentid,username,groupname  (as real fields)
> No, it would look like this:
>
> (1,'jarraa','postgres','keith') to match your original schema.

I dont understand it. what type would it be (the second column) and
what constraint would it have.
And how can I get away with storing just one table. According to my
thougths, I seem to need two tables for certain (could be wrong).
Schemas as follows:

(documentID, From,subject, message ....)
(doucumentID, OneOfTheManyToFieldReciepient)


Keep in mind that I dont know how many entries are in the To field
(its just one string coming from the client!). IT would be helpful to
think of this as just like an email system (but a closed one, as in
you have everybody's username in the db). Furhtermore think of social
networks (thats what I am building).


>
> But it really depends on what you want to solve here.
> What data exactly do you have and how do you want to look
> for data? I mean, what are the keys?
>
I rarely analyze data I recieve (besides for constraints). We have
lucene (java search engine) indexing the db contents and almost all
*SELECT*  queries goto that first and then I get an ID list (since
lucene does not store the data) and I retrieve the information in the
format as the same way it came in.
Lucene, being in java can split up tokens fairly easily and quickly
and our Lucene engineer likes it in the format of string '  name,
name, name'

> Do you have a specific "group" and want to see all "docs" for
> that group? And then you have a user and want to see all "docs"
> for this user? Or is it the other way round and you have
> a "doc" and want to see responsible users and/or groups?
see above explanation of lucene.

> And are groups independend from users? Or are users always
> in one or many groups? ...

A user can belong to many groups. A user can write a "document" To
users, and To groups. The constrainfo user being the user is in the db
and for groups, he/she has to be a member of it.

Users Schema
(username, userInfo)
Group Schema (2 tables)
(groupID, groupname, groupInfo)
(groupID, oneOfTheManyGroupMembers)

Hope this helps and that I am making sense now. Sorry for the confusion.

-assad

On 1/12/06, Tino Wildenhain <tino@wildenhain.de> wrote:
> Assad Jarrahian schrieb:
> > Tino, thanks for your response
> >
> >>Your schema could rather look like this:
> >>
> >>documentid,username,groupname  (as real fields)
> >>
> >
> >
> > Okay, so a typical document can be addressed to any number of users/groups.  so
> > according to an example with the to field = 'jarraa, postgres, keith',
> > some rows could be like
> >
> > (1, jarraa, )
> > (1,  , postgres)
> > (1, keith, )
> >
>
> No, it would look like this:
>
> (1,'jarraa','postgres','keith') to match your original schema.
>
> But it really depends on what you want to solve here.
> What data exactly do you have and how do you want to look
> for data? I mean, what are the keys?
>
> Do you have a specific "group" and want to see all "docs" for
> that group? And then you have a user and want to see all "docs"
> for this user? Or is it the other way round and you have
> a "doc" and want to see responsible users and/or groups?
> And are groups independend from users? Or are users always
> in one or many groups? ...
>
>