i) In ur child table "Holidays" one of the column is not require. Either EndTime or CalculatedDays.
You can Remove EndTime coz user will be apllying for No. Of days with StarTime.
so you can calculate EndTime when require.
ii) Suppose my Leave starttime is '15.07.2010' User=50
Then you relate my input with parent table Year and Aspnetuserid and find out my available holidays and take action accordingly.
You should always post your problem with suitable example in the beginning.
Of what I understood,
i)First you calculate total no of days leave I hv already taken this year.
ii)Then match this with in parent table.
iii)If I can still get leave then leave granted else no.
'year' and child 'starttime' are different
They are similar year wise.
Select year(getdate())==it will give you 2010
Now ask what do you want to know.And give suitable example and what you are expecting.
zipfeli
Member
110 Points
194 Posts
SQL 2008 Relationship suggestion
Oct 06, 2010 04:57 PM|LINK
Hi
How would you relate those 2 tables together?
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 05:57 AM|LINK
I would suggest you to explain the function of both table with sample data.
Then you would get correct answer here.
At first glance it appear that only AspNetUserId is common to both.
Kumar Harsh
zipfeli
Member
110 Points
194 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 06:10 AM|LINK
Thank you for answering Kumar. The parent table available_holidays holds the available holidays per year a user has e.g.
'01.01.2010;15;user'
'01.01.2011;20;user'
Since the dates in child table holidays are different eg. than parent year I don't know how to create constraints. e.g.
'15.07.2010;20.07.2010;user'
'03.03.2011;10.03.2011;user'
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 06:33 AM|LINK
i) In ur child table "Holidays" one of the column is not require. Either EndTime or CalculatedDays.
You can Remove EndTime coz user will be apllying for No. Of days with StarTime.
so you can calculate EndTime when require.
ii) Suppose my Leave starttime is '15.07.2010' User=50
Then you relate my input with parent table Year and Aspnetuserid and find out my available holidays and take action accordingly.
you may need this,
http://forums.asp.net/p/1606373/4103339.aspx#4103339
Kumar Harsh
zipfeli
Member
110 Points
194 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 06:43 AM|LINK
Thank you Kumar.
ii) Since parent 'year' and child 'starttime' are different how can I pair the tables with primary and foreign key for having parent/child relation?
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 07:14 AM|LINK
You should always post your problem with suitable example in the beginning.
Of what I understood,
i)First you calculate total no of days leave I hv already taken this year.
ii)Then match this with in parent table.
iii)If I can still get leave then leave granted else no.
They are similar year wise.
Select year(getdate())==it will give you 2010
Now ask what do you want to know.And give suitable example and what you are expecting.
Kumar Harsh
zipfeli
Member
110 Points
194 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 07:31 AM|LINK
Sorry for being so unprecise.
I want to know how to create primary/foreign key relationship?
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 08:01 AM|LINK
Composite key,
year(starttime) and aspnetuserid of child table=year(year) and aspnetuserid of parent table
with your existing design.
Kumar Harsh
zipfeli
Member
110 Points
194 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 08:31 AM|LINK
Thank you Kumar, but how can I change the constraint to relate only on year from parent table?
zipfeli
Member
110 Points
194 Posts
Re: SQL 2008 Relationship suggestion
Oct 07, 2010 08:31 AM|LINK
By the way I'm open to change design