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

"Erland Sommarskog" <esquel_at_sommarskog.se> wrote in message news:Xns9619F118AB634Yazorman_at_127.0.0.1...

Hi,

Thanks for the reply Erland (and Lee too).

> 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.

I was able to implement this functionality essentially as described.

> 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!)

It isn't so common but it does happen. The clubs don't all agree it should be deleted (we chose to keep it for our club) so, I guess we keep it around. It's only accessible to the creating club in any case and, the player might return if Junior Pop Idol doesn't work out... ;-)

Kunle Received on Thu Mar 17 2005 - 17:06:05 CET