473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Nov 12 '05 #1
3 1102
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" <no******@replyToTheGroup.nospam.org> wrote in message
news:d1**********@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 ===================

Nov 12 '05 #2
Kunle Odutola (no******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Nov 12 '05 #3

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@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

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Khue Pham | last post by:
Hi All, I'm building an embedded system and in need of bringing mySQL into my embedded controller. Here is what I need: + A minimal mySQL database configuration that supports: -> API for C...
0
by: Rob Young | last post by:
This is the latest in the "Total Non-Programmer" series. Any feedback on the usability of this tutorial would be greatly appreciated. Thanks, Rob How to Build A Web Database (Without...
6
by: H | last post by:
This is a question that has haunted me for quite some time. if you build a 4 tier database application where the 4th tier is the database server (MS SQL 2000), where do you build the connection...
15
by: grunar | last post by:
After some thought on what I need in a Python ORM (multiple primary keys, complex joins, case statements etc.), and after having built these libraries for other un-named languages, I decided to...
0
by: jonathan.beckett | last post by:
Just a quick message letting those who are interested know that a new version of the PluggedOut Blog script is available for download - more bugs have been squashed, and a new sample theme has been...
6
by: LurfysMa | last post by:
I am working on an electronic flashcard application. I have one version up and running using Visual Basic (6.0) and Access 2000. My long-range plans are to put it up on a website and sell...
6
by: iKiLL | last post by:
Hi all I am developing in C#, CF2 and SQL Mobile. Currently my app is using Merge Replication. This is all working well. I have now decided to try and use Result sets in my application but I...
9
by: =?Utf-8?B?TUNN?= | last post by:
I'm sure the answer to my question varies depending on the situation, but I am looking for a general "best practice". If I have an asp.net application and I load certain data from a database,...
0
by: Akira Kitada | last post by:
Hi Marc-Andre, Thanks for the suggestion. I opened a ticket for this issue: http://bugs.python.org/issue4204 Now I understand the state of the multiprocessing module, but it's too bad to see...
0
by: M.-A. Lemburg | last post by:
On 2008-10-25 20:19, Akira Kitada wrote: Thanks. The errors you are getting appear to be related to either some missing header files or a missing symbol definition to enable these - looking...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.