Design question [Using MS SQL Server]

Prev: restore backup and roll forward versus detach/attach?
Next: Place a message in the outbox of Outlook

From: Edward on 22 Feb 2010 10:28

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

From: Nilone on 22 Feb 2010 11:46

On Feb 22, 5:28 pm, Edward <teddysn...(a)hotmail.com> wrote:
> 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

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: Sylvain Lafontaine on 22 Feb 2010 12:34

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(a)gmail.com> wrote in message
news:1ae938cb-bdd7-4e01-a43c-86a6d9fa62b3(a)l26g2000yqd.googlegroups.com...
On Feb 22, 5:28 pm, Edward <teddysn...(a)hotmail.com> wrote:
> 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

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: Geoff Muldoon on 22 Feb 2010 17:05

Edward says...
> 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).

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

From: Geoff Muldoon on 22 Feb 2010 18:04

| Next | Last
Pages: 1 2
Prev: restore backup and roll forward versus detach/attach?
Next: Place a message in the outbox of Outlook