Re: How to build database to support user-specified attributes?

Kunle Odutola said: > 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"). I'd look into the inheritance capabilities of IDS 9.x to do this. > 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 =================== > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco Travel broadens a person. You look as if you have been all over the world. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed http://members.rogers.com/2remedy/images/smilies/banners/RTFM.gif sending to informix-list