October 28, 2012 at 4:45 am
Was hoping someone could kick me in the right direction in regards stored procedures.
My issue is if I have a table that utilises foreign keys for other tables how do I update or use that data. Specifically I have a database for sports I want to enter a draw so teams matches in rounds at venues over a season. So i created a draw table
CREATE TABLE [dbo].[Draw](
[DrawId] [int] IDENTITY(1,1) NOT NULL,
[RoundNumberFk] [int] NULL,
[HomeTeam] [nchar](20) NULL,
[AwayTeam] [nchar](20) NULL,
[VenueFk] [int] NULL,
[Date] [datetime] NULL,
[TeamListFk] [int] NULL,
CONSTRAINT [PK_Draw] PRIMARY KEY CLUSTERED
(
As you can see there is a RoundNumberFK field. This comes from round.
CREATE TABLE [dbo].[Round](
[RoundID] [int] IDENTITY(1,1) NOT NULL,
[RoundNumber] [smallint] NULL,
CONSTRAINT [PK_Round] PRIMARY KEY CLUSTERED
( Round goes to a join SeasonRound I have entered the round numbers and season details into that table already. No I want to enter in the teams home & away and the round and venue they are in.
In other words if I have inputted "season 2013" into the seasons table and added 1-26 in the rounds table, when i am updating the draw in the draw table, how to I use season 2013 with round 1 and add the teams to it so that they all match up?
So how do I link use the data from the round & season details to enter the match details?
Since I will consistently do this should it be a stored procedure?
October 28, 2012 at 11:32 pm
Is there any extra info I could provide that would help
October 29, 2012 at 1:18 am
I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan. 🙂
However you have two fields shown as:
[RoundNumberFk] [int] NULL,
[TeamListFk] [int] NULL,
Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.
Here's a pretty good tutorial on doing that:
http://www.w3schools.com/sql/sql_foreignkey.asp
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 29, 2012 at 4:03 am
dwain.c (10/29/2012)
I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan. 🙂However you have two fields shown as:
[RoundNumberFk] [int] NULL,
[TeamListFk] [int] NULL,
Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.
Here's a pretty good tutorial on doing that:
Thanks I have defined the foreign keys already. I just don't know how to access them.
So to my logic to reduce the replication of fond numbers I created a round table and season table.
This allows me to say for years 2011, 2012, 2013 all featured 10 rounds. Each round has say 8 matches of 2 teams. It's doing this in the draw table that is causing me bother, so as an example this is the data I want to enter using the season and round tables but can't:
Season: 2012
Round: 1
Venue: Canberra
HomeTeam: Canberra
AwayTeam: Melbourne
But how do I use the season and round details from their tables when entering draw details?
October 29, 2012 at 4:39 am
how about trigger on primary or on Child table base on your requirment.
why you want to use foreign key?
foreign key is for the relation and CONSTRAINT on table
October 29, 2012 at 4:48 am
BriPan (10/29/2012)
how about trigger on primary or on Child table base on your requirment.why you want to use foreign key?
foreign key is for the relation and CONSTRAINT on table
> how about trigger on primary or on Child table base on your requirment.
I don't really understand what this means to respond properly.
I created a database design like a snowflake design everything comes to one central table games. This is the first database I have designed.
> why you want to use foreign key?
Well I thought I had to relate the tables, since draws are done for seasons and seasons are defined in rounds. Games and draws are separate as draws are for most part future and games are past(historical).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply