Normalize or not? - Mailing list pgsql-general

Based upon my reading of wikipedia, the solution I think I want to implement is not in 2nd normal form.  I'm wondering
ifanyone on this list has suggestions, etc. 

I have a table called containers where object A will contain object B.  There is a middle column that will describe the
typeof association.  For example, the account team will have Joe as a member and Fred will be the lead.  This will
startsoff with: 

Containers:
Left How Right
Account member Joe
Account lead Fred

There is another table (which I've talked about before) which tells me that "Account" is a "Team".  I call this the
namestable and has two columns: name and type.  So: 

Names:
Name Type
Account Team
Joe User
Fred User

in this case.  I want to restrict the type of association e.g. I want teams to contain users but not allow users to
containteams.  And I want some type of associations not apply to others like a Group can simply "contain" a Team but a
Teamcan not contain a Group.  Thus, I have a third table with entries like: 

Types of Associations:
LType How RType
Team member User
Team lead User
Group contain Team

There is a foreign key constraint so that Containers(Left) and Containers(Right) must be in Names(Name) (I hope that
syntaxmakes sense).  But now comes the hard part. 

For each row in Containers, I want to take the Type from Names associated with the Left + Containers(How) + the Type
fromNames associated with the Right to be an entry in the Types of Associations table.  For eacmple, Account member Joe
wouldtranslate to Team member User which is in the Types of Associations table so it is a valid entry.  But I don't
believeI can do this with foreign key constraints.  If I can, then stop me here and tell me how. 

My solution to solving this that I am considering is to add in the LType and RType into the Containers so now
Containersbecomes: 

Containers:
Left LType How Right RType
Account Team member Joe User
Account Team lead Fred User

and now my foreign key constraint is simply that Containers(Ltype),Containers(How),Containers(RType) be in Types of
Association. I would also change my constraint so that Left,LType of Containers must be in Names as well as Right,RType
bein Names. 

According to my interpretation, the RType and LType columns of Containers are non-prime but are functionally dependent
uponeither Left or Right so this table is not even second normal form.  But, if I add in the constraint that both the
Nameand Type must be in Names, does it really matter?  I know that I will not be able to change just LType or just
RTypeand create an update anomaly.  I guess I should mention that change the Type of a name is not even being
considered. The "Names" and "Types of Associations" tables I think of as a type of constants which will be defined and
rarelychange. 


I hope this is reasonably easy to follow.  I'm looking forward to your thoughts and comments.

Thank you,
Perry Smith