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

At its simplest, you would have the following tables, one to store the custom attribute classes for each club and the other to store the instances that have been assigned players :

CREATE TABLE ClubAttribute (

      Club_ID              int NOT NULL,
      Attribute             varchar(100) NOT NULL

      Player_ID              int NOT NULL,
      Attribute             varchar(100) NOT NULL,
      Value             varchar(100) NOT NULL

a club can contain 0 or more attributes and the playerattribute can containg any number of attributes for each of any number of players. The logical attribute table itself contains only the one column and does not need to be physically present in the DB. If you wanted to expand further on this example you can consider typing each class.

Mr Tea

"Kunle Odutola" <noemails_at_replyToTheGroup.nospam.org> wrote in message news:d14iv7$fqk$2_at_sparta.btinternet.com...
>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 ===================
>
Received on Mon Mar 14 2005 - 21:37:04 CET