January 5, 2017 at 12:45 pm
Something a little more along these lines...
CREATE TABLE dbo.Player (
PlayerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL
CONSTRAINT pk_Player PRIMARY KEY CLUSTERED (PlayerID)
);
CREATE TABLE dbo.Sport (
SportID INT NOT NULL,
SportName VARCHAR(20) NOT NULL,
CONSTRAINT pk_Sport PRIMARY KEY CLUSTERED (SportID)
);
CREATE TABLE dbo.Sport_Player (
PlayerID INT NOT NULL,
CONSTRAINT fk_SportPlayer_PlayerID FOREIGN KEY (PlayerID) REFERENCES dbo.Player(PlayerID)
ON UPDATE CASCADE
ON DELETE CASCADE,
SportID INT NOT NULL,
CONSTRAINT fk_SportPlayer_SportID FOREIGN KEY (SportID) REFERENCES dbo.Sport(SportID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT pk_SportPlayer PRIMARY KEY CLUSTERED (PlayerID, SportID)
);
January 5, 2017 at 3:10 pm
Having a new sport shouldn't require a DDL change. Getting a list of available sports shouldn't need to remove duplicates.
I would make the argument that the list of available sports is relatively static. Therefore, it is a good thing to avoid the overhead of cascaded actions, asked her indexing, etc. do you really expect your school to add a 43-Man Squamish team? Even if they wanted to, the expense of setting up a Squamish field will probably stop them 🙂 likewise, dropping a sport from your offerings is also fairly rare.
However, players for the games we do support will come and go fairly often.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 5, 2017 at 3:42 pm
CELKO (1/5/2017)
Having a new sport shouldn't require a DDL change. Getting a list of available sports shouldn't need to remove duplicates.
I would make the argument that the list of available sports is relatively static. Therefore, it is a good thing to avoid the overhead of cascaded actions, asked her indexing, etc. do you really expect your school to add a 43-Man Squamish team? Even if they wanted to, the expense of setting up a Squamish field will probably stop them 🙂 likewise, dropping a sport from your offerings is also fairly rare.
However, players for the games we do support will come and go fairly often.
But your approach makes it much more difficult to determine which sports are no longer being supported. You can't simply run a report of available sports that have no current players, because your available sports are hidden in a check constraint.
I see absolutely no benefit to having this as a check constraint rather than as a foreign key constraint.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 7, 2017 at 5:08 pm
I see absolutely no benefit to having this as a check constraint rather than as a foreign key constraint.
The check constraint can be used by the optimizer immediately; it is a predicate. The foreign key has to go to the referenced table and has more overhead. Is it locked, or shared? Etc.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 9, 2017 at 8:51 am
CELKO (1/7/2017)
I see absolutely no benefit to having this as a check constraint rather than as a foreign key constraint.
The check constraint can be used by the optimizer immediately; it is a predicate. The foreign key has to go to the referenced table and has more overhead. Is it locked, or shared? Etc.
I notice that you avoided the question of how to produce a report of sports that do not currently have any participants.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 9, 2017 at 9:53 am
drew.allen (1/9/2017)
CELKO (1/7/2017)
I see absolutely no benefit to having this as a check constraint rather than as a foreign key constraint.
The check constraint can be used by the optimizer immediately; it is a predicate. The foreign key has to go to the referenced table and has more overhead. Is it locked, or shared? Etc.
I notice that you avoided the question of how to produce a report of sports that do not currently have any participants.
Drew
It's because that's so simple that it's not even worth of the great master to answer. You just need to query the system views and dissect the check constraint definition. It would turn into a simple query like this:
SELECT Item AS Sport_Name
FROM (
SELECT REPLACE( REPLACE( REPLACE( cc.definition, ''' OR [sport_name]=''', CHAR(7)), '([sport_name]=''', ''), ''')', '') SportsNames
FROM sys.check_constraints cc
JOIN sys.columns c ON cc.parent_object_id = c.object_id AND cc.parent_column_id = c.column_id
JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = 'Sports'
AND c.name = 'sport_name') AS query
CROSS APPLY dbo.DelimitedSplitN4K( SportsNames, CHAR(7)) s;
Of course, JC would use the INFORMATION_SCHEMA views, because they're part of the SQL standard. However, my knowledge is not enough to know how to identify the constraint from there because it doesn't have a name assigned on the DDL.
January 9, 2017 at 10:29 am
I notice that you avoided the question of how to produce a report of sports that do not currently have any participants.
Here is a little different way of doing it, with sure set operations (a table constructor and set difference). I have no idea if this is faster than outer join, but it looks a little nicer.
SELECT X.sport_name
FROM ((VALUES ('Football'), ('Soccer'), ('Baseball'),
('Basketball'), ('Volleyball'), ('Fuseball'))
EXCEPT
(SELECT sport_name FROM Sports))
AS X(sport_name);
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 9, 2017 at 10:33 am
Of course, JC would use the INFORMATION_SCHEMA views, because they're part of the SQL standard. However, my knowledge is not enough to know how to identify the constraint from there because it doesn't have a name assigned on the DDL.
Actually, I would do a cut–and-paste to get the list of sports available; this particular list is just too short to go to all that trouble 😀 but you are right; I tend not to name constraints when I am posting on forums is usually so few of them and I am getting lazy in my old age. Yes, we really should name all of our constraints, except maybe primary keys, which are easy to find.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply