Re: sql (Stored procedure) design question

From: Assad Jarrahian <jarraa(at)gmail(dot)com>
To: Tino Wildenhain <tino(at)wildenhain(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: sql (Stored procedure) design question
Date: 2006-01-12 08:02:23
Message-ID: 4bd3e1480601120002x460961cam7ad15d83b8f1852d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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, )

so I am not sure if I can make foreign keys constraints here (since
the username or group can be blank). Additionally the to field can be
to any user, but can only be to a group that the user has permissions
to write to (aka subscribed).

> >
> > 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)

cat them because originally I wanted really fast retrieval (with the
method you describe, I will have to go to two tables), so I would
store it in the order it was to be shipped out (much like an email to
field when you recieve it)

Where can I find this text concatenation stuff?

>

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

hehe

maybe I can do a hybrid, storing the string completely in the doc
table and storing all the to fields as a document_to table.

Awaiting your thoughts.
thanks!

-Assad

>
>
> ...
> >
> >
> > 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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2006-01-12 11:23:36 Re: sql (Stored procedure) design question
Previous Message Tino Wildenhain 2006-01-12 07:53:42 Re: sql (Stored procedure) design question