Re: sql (Stored procedure) design question

Assad Jarrahian schrieb:
> what drove me to store it that way was more of a performance issue.
>
> So if I store a documentID and then have a seperate table names, to_field
> I will have to store the username/groupname as such
> to_field:
> documentID username
> docuementID groupname
> docuementID usernamename
>
>From what I see below (btw, try to follow the way people cite on
mailinglists)

Your schema could rather look like this:

documentid,username,groupname (as real fields)

>
> based on that it seems that to read (Which occurs a lot more than
> writing) a document I will have to go an find all the rows with
> documentID in the to field and then cat them somehow and then return

cat them? Why? (There is text concenation btw)

> the whole document. Even with an index built on the documentID of the
> to field, it seems to be resource intensive (correct me if I am
> wrong). Even if I were to do this, I would still have to check the
> constraints by hand (since the entry can either be a username or a
> groupname (and if its a group, the user has to belong to that group).

Well no, as above, you would have the usual foreign keys.
>
> So coming back to the original synopsis. Is there a way I can just
> send the whole document to a stored procedure and then the stored
> procedure takes the text component of it and tokenizes it and then I
> do constraint checks on it?

There is ;) But I doubt Tom likes to show you the dark side [tm] ;)

...
>
>
> On 1/11/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>>Assad Jarrahian <jarraa(at)gmail(dot)com> writes:
>>
>>> A column of type text contains username's and groupname's followed
>>>by comma (eg. 'jarraa, mooreg3, keith') [it is stored this way
>>>because it will be displayed in this format].

++Tino