XPost: comp.databases.ibm-db2, comp.databases.informix, comp.dat   
   bases.ms-access   
   XPost: comp.databases.ms-sqlserver, comp.databases.postgresql.sql,   
   comp.databases.sybase   
   From: noemails@replyToTheGroup.nospam.org   
      
   "Erland Sommarskog"  wrote in message   
   news:Xns9619F118AB634Yazorman@127.0.0.1...   
      
   Hi,   
      
   Thanks for the reply Erland (and Lee too).   
      
   > It seems that you would have:   
   >   
   >   CREATE TABLE Attributes (ClubID      int  NOT NULL,   
   >                            AttributeID int  NOT NULL,   
   >                            AttributeText varchar(80) NOT NULL,   
   >                            typeofdata  char(1) NOT NULL   
   >                              CHECK (typeofdata IN ('I', 'V', 'D', B')),   
   >                            PRIMARY KEY (ClubID, AttributeID),   
   >                            FOREIGN KEY (ClubID)   
   >                                  REFERENCES FootballClub(Club_ID))   
   >   go   
   >   CREATE TABLE AttributeValues   
   >          (ClubID      int NOT NULL,   
   >           PlayerID    int NOT NULL,   
   >           AttributeID int NOT NULL,   
   >           charval     varchar(255) NULL,   
   >           dateval     datetime NULL,   
   >           bitval      bit NULL,   
   >           intval      int NULL,   
   >           PRIMARY KEY (ClubID, PlayerID, AttributeID),   
   >           FOREIGN KEY (PlayerID) REFERENCES Player (Player_ID),   
   >           FOREIGN KEY (ClubID, AttributeID)   
   >              REFERENCES Attributes (ClubID, AttributeID´))   
   >   
   > The idea with typeofdata and the xxxval columns is that you could permit   
   > different sorts of attributes and store them in appropriate columns.   
   > If you are using SQL Server, you can use the sql_variant datatype to   
   > have a single value column.   
      
   I was able to implement this functionality essentially as described.   
      
   > There is redundancy in the table, in that the players club affiliation is   
   > repeated here. For a while I was thinking that Player was incorrectly   
   > designed; it should really have (ClubID, PlayerID) as key. But since a   
   > player could change clubs, this is not so good. Then again, if a player   
   > changes clubs, you will need to erase all attributes for a player. (Given   
   > that this is about kids, one would hope that transfers are not that   
   > common!)   
      
   It isn't so common but it does happen. The clubs don't all agree it should   
   be deleted (we chose to keep it for our club) so, I guess we keep it around.   
   It's only accessible to the creating club in any case and, the player might   
   return if Junior Pop Idol doesn't work out...  ;-)   
      
   Kunle   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)