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   
      
   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.   
      
   Has anyone done anything similar?. Any ideas on how it might be done?   
      
   Kunle   
      
      
   =================== BEGIN DDL ===================   
   CREATE TABLE FootballClub (   
          Club_ID              int IDENTITY,   
          Name                 char(80) NOT NULL,   
          Area                 char(4) NOT NULL,   
          League              char(4) NOT NULL,   
          City                 char(30) NOT NULL,   
          PRIMARY KEY (Club_ID)   
   )   
   go   
      
   exec sp_primarykey FootballClub,   
          Club_ID   
   go   
      
   CREATE TABLE Player (   
          Player_ID            int IDENTITY,   
          First_Name           char(30) NOT NULL,   
          Initials             char(30) NULL,   
          Last_Name            char(30) NOT NULL,   
          Date_Of_Birth        datetime NOT NULL,   
          Position             char(4) NULL,   
          Club_ID              int NULL,   
          PRIMARY KEY (Player_ID),   
          FOREIGN KEY (Club_ID)   
                                REFERENCES FootballClub   
   )   
   go   
      
   exec sp_primarykey Player,   
          Player_ID   
   go   
      
   CREATE TABLE UserAccount (   
          User_ID              int IDENTITY,   
          Club_ID              int NOT NULL,   
          FullName             char(80) NOT NULL,   
          Logon                char(20) NOT NULL,   
          PWD_Hash             char(60) NOT NULL,   
          PRIMARY KEY (User_ID, Club_ID),   
          FOREIGN KEY (Club_ID)   
                                REFERENCES FootballClub   
   )   
   go   
      
   exec sp_primarykey UserAccount,   
          User_ID,   
          Club_ID   
   go   
      
   exec sp_foreignkey Player, FootballClub,   
          Club_ID   
   go   
      
   exec sp_foreignkey UserAccount, FootballClub,   
          Club_ID   
   go   
   =================== END DDL ===================   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)