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: esquel@sommarskog.se   
      
   Kunle Odutola (noemails@replyToTheGroup.nospam.org) writes:   
   > I have a database that tracks players for children's sports clubs. I have   
   > included representative DDL for this database at the end of this post.   
   >   
   > A single instance of this database supports multiple clubs. I would like   
   > to add support for letting each club define and store custom information   
   > about their players. Basically, allows the clubs to define custom   
   > attributes for players (e.g. "height", "weight", "favourite sweet").   
   >   
   > A few constraints:   
   > 1. Any attributes defined is "private" to the defining club. Other clubs   
   > aren't aware of it although they may define custom attributes of their   
   > own with the same name and type. [Perhaps there is a way to share   
   > definitions of identical attributes?]   
   > 2. A club doesn't have to define any custom attributes.   
      
   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.   
      
   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!)   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server SP3 at   
   http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)