Based upon my reading of wikipedia, the solution I think I want to implement is not in 2nd normal form. I'm wondering if anyone 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 type of association. For example, the account team will have Joe as a member and Fred will be the lead. This will starts off 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 names table 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 contain teams. And I want some type of associations not apply to others like a Group can simply "contain" a Team but a Team can 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 syntax makes 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 from Names associated with the Right to be an entry in the Types of Associations table. For eacmple, Account member Joe would translate to Team member User which is in the Types of Associations table so it is a valid entry. But I don't believe I 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 Containers becomes: 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 be in Names. According to my interpretation, the RType and LType columns of Containers are non-prime but are functionally dependent upon either Left or Right so this table is not even second normal form. But, if I add in the constraint that both the Name and Type must be in Names, does it really matter? I know that I will not be able to change just LType or just RType and 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 rarely change. I hope this is reasonably easy to follow. I'm looking forward to your thoughts and comments. Thank you, Perry Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
