I've been tasked with designing an application to capture data about
sports clubs.

Let's say there are four different types of club - Soccer, Cricket,
Rugby and Tennis. For various reasons it is essential that data about
each of these will be stored in its own table.

I want to make use of the new datatype Geography to store the location
data. Unfortunately, LINQ to SQL doesn't yet support these. So my
thinking is to keep the location data in a separate table and
manipulate the data server-side to enable CRUD operations, but
ensuring that messages containing location information are in a form
that Visual Studio can deal with (by parsing in UDFs etc).

Here's the question. Would it be acceptable practice to have a
single, over-arching Location table which would store the location
data with a unique key consisting of the TYPE of the club PLUS the
UNIQUE ID of the club? Assuming a table of ClubType where Soccer =1,
Cricket = 2, Rugby = 3 etc. In the SoccerClub table let's say Hanley
Rovers has a UniqueID = 1. So, to get the location information;

SELECT LocationData from Location WHERE ClubType = 1 AND ClubID = 1

The alternative would be to have a Location table for each club type -
e.g. SoccerLocation, CricketLocation etc. with a 1:1 relation.

Thoughts? Apologies if I haven't expressed this very well.

Edward

On Feb 22, 5:28*pm, Edward <teddysn... (AT) hotmail (DOT) com> wrote:
I would prefer something like

Club (Id uniqueidentifier, ClubName nvarchar)
ClubLocation (Id uniqueidentifier references Club (Id), LocationData
geography)
SoccerClub (Id uniqueidentifier references Club (Id))
CricketClub (Id uniqueidentifier references Club (Id))
RugbyClub (Id uniqueidentifier references Club (Id))
TennisClub (Id uniqueidentifier references Club (Id))

Of course, there are caveats with such a design, as with any other.

From the moment that he have chosen to separate the clubs into separate
tables, he is introducing caveat into his design but at least, this way,
relationships and the capability of the query engine to make the best usage
of indexes is preserved while still maintaining the number of tables at a
minimum.

Using a single field plus one type field is probably the worst solution in
term of performance because the same field can now contains multiple
foreign keys; thus removing the capabilities of seting up proper
relationships and greatly reducing the possibility of using indexes.

However, if this table is small and only has a few records and probably not
used very often; then making a full scan each time he need to join to it
won't be necessarily a big problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Nilone" <reaanb (AT) gmail (DOT) com> wrote

On Feb 22, 5:28 pm, Edward <teddysn... (AT) hotmail (DOT) com> wrote:
I would prefer something like

Club (Id uniqueidentifier, ClubName nvarchar)
ClubLocation (Id uniqueidentifier references Club (Id), LocationData
geography)
SoccerClub (Id uniqueidentifier references Club (Id))
CricketClub (Id uniqueidentifier references Club (Id))
RugbyClub (Id uniqueidentifier references Club (Id))
TennisClub (Id uniqueidentifier references Club (Id))

Of course, there are caveats with such a design, as with any other.

Edward says...
My view:

create table locations (location_id, location_information, ...)
create table club_types (club_type_id, club_type_desc)
create table clubs (club_id, club_name, club_type_id, ...)
create table location_suitability (location_id, club_type_id, ...)
create table club_locations(club_type, club_id, location_id, ...)

Why? Because there is the possibility (probability?) that clubs might
share a location (site suitable for both rugby and soccer for example).
And perhaps a club may have multiple locations.

Depending on scope, you might even have a location_activity_type (playing,
training, admin) table.

If your "various reasons" produce an overwhelming argument, then and only
then split the clubs into separate tables per type.

GM

Edward says...
My view:

create table locations (location_id, location_information, ...)
create table club_types (club_type_id, club_type_desc)
create table clubs (club_id, club_name, club_type_id, ...)
create table location_suitability (location_id, club_type_id, ...)
create table club_locations(club_type, club_id, location_id, ...)

Why? Because there is the possibility (probability?) that clubs might
share a location (site suitable for both rugby and soccer for example).
And perhaps a club may have multiple locations.

Depending on scope, you might even have a location_activity_type (playing,
training, admin) table.

If your "various reasons" produce an overwhelming argument, then and only
then split the clubs into separate tables per type.

GM

On 22 Feb, 15:28, Edward <teddysn... (AT) hotmail (DOT) com> wrote:
Thank you one and all. Most instructive.

Edward

1) do NOT use GUIDs - astoundingly bad for performance
2) do not use LINQ to SQL, unless you are strictly doing stored procedure
access. And even then don't use it. :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Edward" <teddysnips (AT) hotmail (DOT) com> wrote