From: Edward on
On 22 Feb, 15:28, 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

Thank you one and all. Most instructive.

Edward
From: TheSQLGuru on
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(a)hotmail.com> wrote in message
news:ee20cb31-cb6d-4ae3-8a01-7d9165a749c9(a)q21g2000yqm.googlegroups.com...
> 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