Re: Composite UNIQUE across two tables?

Em Thursday 06 March 2008 22:10:14 Jamie Tufnell escreveu:
>
> I already have site_id in the users table and I can determine the
> site_group_id from that.  So it seems redundant to me, to store
> site_group_id for each user as well.   Also, I'm not sure how I would
> enforce that the site_group_id added to the users table would
> correspond correctly with the site_id (as per the sites table).
> Perhaps I would make a composite foreign key?

Or you manage site_group_id from a trigger.  When the user creates / chooses a 
site_id and updates/inserts this information then you fill site_group_id with 
a trigger.  This way you guarantee the correctness of this parameter.

> I've never come up against this situation before, and because of the
> reasons above, I'm getting the feeling there might be a better way to
> design this.

Again, a trigger can do that.  Slower than denormalizing the database, but it 
can be done.


-- 
Jorge Godoy      <jgodoy@gmail.com>