How to build database to support user-specified attributes?

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

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
)
CREATE TABLE PlayerAttribute (
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" wrote in message
news:d14iv7$fqk$2@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 ===================
>

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
)
CREATE TABLE PlayerAttribute (
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" wrote in message
news:d14iv7$fqk$2@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 ===================
>

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
)
CREATE TABLE PlayerAttribute (
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" wrote in message
news:d14iv7$fqk$2@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 ===================
>

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

Kunle Odutola (noemails@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))
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.

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@sommarskog.se

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

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

Kunle Odutola (noemails@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))
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.

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@sommarskog.se

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

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

Kunle Odutola (noemails@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))
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.

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@sommarskog.se

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

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

"Erland Sommarskog" wrote in message
news:Xns9619F118AB634Yazorman@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

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

"Erland Sommarskog" wrote in message
news:Xns9619F118AB634Yazorman@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

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

"Erland Sommarskog" wrote in message
news:Xns9619F118AB634Yazorman@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