A design pattern for dynamic allocation and removal.
-
Over the years I have come across several occasions that require that an entity (lets say players) is a part of another entity(lets say team).
Players may be on bench or unavailable due to some sickness and then manage to come back. A similar situation is in the case where rates for some service may be loaded and are valid till a certain date. If no date is specified it is valid to infinity.
The challenge is to handle this kind of a dynamic change in validity and assoication of one entity to another.
A simple way to solve this is as follows
in the child table(players or rates)
Enter two new columns, Associated from and associated to.
When entering values if no assoicated to date is supplied enter infinity(31-Jan-9999) in case of sql server, the associated on date could be today.
The trick is to use the largest possible date, I have come across designs where null is used in place of this..and this makes using the between and < operator very difficult(in case u want to pull out the players in a team for a particular period of time).
following is the sql for handling added, removed and new added associations
--AssocTab->The table that hold the association between child and parent
--#TempList contains all the child ids, like PlayerId, studentId etc(I was coding something similar,
--and just decided to change table names. I was passing a comma seperated list of child ids and parsing
--that into a table hence the temp table prefix)
--check if there is any new association, to be inserted
--into the parent_child association table(this is a 1 to many relationship)
select childId
into #NewAssoc
from #Templist --this contains all the new children of a particular parent
where childId
not in(
select AssocTab.ChildId
from AssocTab
where AssocTab.ParentId= @ParentId
and AssociatedTo > getdate()--only current associations
 
insert into AssocTab(ParentId,
ChildId,
AssociatedFrom,
AssociatedTo,
User_Id)--in case u want to track who did it
select @ParentId,
#NewAssoc.ChildId,
getdate(),
'01/01/9999',
87
from #NewAssoc
drop table #NewAssoc
--check if there is any association that has been removed
--these associations are to be closed. so update the
--associated till date.
update AssocTab
set AssociatedTo = getdate()
where ChildId in (
select ChildId
from AssocTab
where ChildId
not in(
select ChildId
from #Templist
 
and AssociatedTo > getdate()--only current associations
 
and Parent = @ParentId--only this terminal
drop table #Templist
Comments and sql optimisations are welcome. Also if you have come across similar posts before, let me know, I havent; but incase some1 has blogged something like this before me, just want to know that i did not copy it from there
..I previewed this and some brackets have come as smilies, so they might give syntax errors.
" Long you live and high you fly
And smiles you'll give and tears you'll cry
And all you touch and all you see
Is all your life will ever be. "
-Syd Barett
-
This was removed by the editor as SPAM
-
Well, actually, I don't really agree. Usually, in the case where you have Teams and Players (or maybe even Companies and Contacts) every designer tends to do what you are doing. They want hard-core Foreign Keys back and forth to the entities.
This is always a flawed design. Lets take the case for Companies and Contacts. It's possible to have a Contact which is not really associated with a Company (ex. My mom). It's also possible to have a Company which doesn't have any contacts (yet) because you haven't gotten your foot in the door (so to speak). The same goes for Players and Teams. I could be a free-lance player looking for a Team. I'm still a Player, but I shouldn't be linked to a team.
In these cases, I usually always create the entities as Many-to-Many relationships. This allows me to have both types of entities with complete disregard of each other. This is usually acceptable because users want to look at the data one way or another, and there are always views for the user to see "who isn't assigned to a Team"? Now, is it possible that a Player belongs to more than one team, maybe not, but they may be in "transition" so maybe so. It's definately possible that a contact belongs to more than one Company. If I'm a consultant, I may have my own company but also currently I am a DBA / Developer for another Company.
As for the Date thing, I tend to add them as NULLs and during lookup do a:
Select
From sometable
Where @Date Between CreatedDate And IsNull(ExpireDate, '12/31/9999')
Just some thoughts...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply