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

Kunle Odutola (noemails_at_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))

         (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_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp