Hi Dirk,
On 3/11/08, Dirk Jagdmann <jagdmann@gmail.com> wrote:
> I vote against duplicating site_group_id in the users table and the
> proposed unique constraint with a function. Because all those might
> fail, if you ever want to change the relationship between a site and a
> site group.
Good point!
> My advise would be to have two triggers for insert/update on the site
> and users table that check the uniqueness of the username with the
> site_group. A have made some tests with inserts and updates on the
> existing users and sites and these two functions seem to work.
I think this is the way that I'll go. I'd hoped to somehow express this
solely in the design, if you know what i mean (e.g. without writing
SPs), but it looks like this is the best way to do it.
> One remark about your schema: If you use PostgreSQL, use the "text"
> datatype for strings, since you don't limit yourself with the string
> length.
[snip]
For some reason I assumed varchar had an advantage over text,
but a quick check of the docs suggests that's not the case. Thanks
for this tip! :-)
Thank you for your taking the time to write this up, it's very much
appreciated.
Cheers,
J.