July 22, 2009 at 3:05 am
I have 2 tables: Events and Workers
An event has a KeyWorker and a CoWorker
The KeyWorker will always be an entry from Workers
The CoWorker can be either be a Worker or an Agency - in the latter case, there will be an AgencyName entry in the Events table (there will be no separate table for Agencies, they want to be able to enter any old text at the application level)
I am unsure of the best way to design the Events table. I currently have:
Events
(
EventId,
KeyWorkerId,
CoWorkerId,
AgencyName,
IsAgency
)
This results in having to run a SELECT like below:
SELECT ...
FROM Events e
JOIN Workers kw ON e.KeyWorkerId = kw.WorkerId
LEFT OUTER JOIN Workers cw ON e.CoWorkerId = cw.WorkerId
Is there a cleverer way to design for this situation?
July 22, 2009 at 3:16 am
maybe you can even skip the isAgency column.
...AgencyName,
IsAgency ...
Implement a constraint to check on the combination coworkerid and agencyname
if coworkerid not is null, agencyname must be null
and the other way around
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 22, 2009 at 3:26 am
July 22, 2009 at 3:45 am
be sure to implement FK-indexes for the columns KeyWorkerId and CoWorkerId.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 22, 2009 at 5:12 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply