April 3, 2010 at 8:20 am
Hello,
Let's assume that we have to book session for individual clients and for group of clients
- first problem is that one client can be assigned to zero or many groups
- second problem is the attendance, which means that every client in group must be treated as individual client
my solution is:
<PK FK> - composite key
I'm using inheritance for Client(PersonID) from Person(PersonID)
BookingDetail (BookingDetailID <PK>,BookingID <FK>, SessionID<FK> ...)
Booking(BookingID <PK>, BookingType <FK> (group or single) ... )
BookingIndividual(BookingID <PK FK>, ClientID <PK FK> ...)
BookingGroup(BookingID <PK FK>, GroupID <PK FK>...)
Should the attendance table look like
Attendance(SessionID <PK FK>, PersonID<PK FK>,TimeIn, TimeOut...)
or
Attendance(AttendanceID<PK>, SessionID<FK>, PersonID<FK>)
and why ??
now BookingID = 1 and BookingType = 'group'
next BookingGroup = 1 and GroupID = 1
I'm setting trigger on BookingGroup :
create trigger groupIndiv_trg
on BookingGroup
after insert
as
set nocount on;
declare @bid int,
@groupid int,
@rowCount int,
@row int,
@clientid int
select top 1 @bid = i.BookingID
from inserted i
select @groupid = GroupID
from BookingGroup
where BookingID = @bid;
declare @group TABLE
(
RowID int IDENTITY(1,1),
ClientID int
);
insert into @group (ClientID)
select PersonID
from Group_mm_Client
where GroupID=@groupid;
set @rowCount = @@ROWCOUNT;
set @row = 0;
while @row < @rowcount
begin;
set @row = @row + 1;
select @clientid=ClientID
from @group
where RowID=@row
insert into BookingIndividual
values(
@bid,
@clientid)
end;
What do you think about the design???
Is it worth doing triggers like that in database or .NET for instance (C#) should solve it
Any comments or ideas will be be much appreciated
April 5, 2010 at 11:19 am
April 5, 2010 at 1:02 pm
Was the client informed about the date and time of the session??(probably letter will be send to inform the client ,not email)
Did the client attended the class??
Was the client late(exact time) if so how often is he late??
...
For letter I will have another table with LetterID <PK>, ClientID <FK> SessionID<FK> and DateSent, DateDelivered and was thinking about putting informed flag in somewhere updated with 1 [trigger] after the client confirm that he/she received the letter and will be able to attend the session but I'm not sure what will be the best place for Informed column. In BookingIndividual this will violate 2nd normal form rule beacause composite key and in BookingDetails there are no individual records for clients only for Bookings that were made hmmm...
those are the main problems.
I have few solutions but was looking for some opinions, new ideas or recomendations
many thanks ,
MARCIN
April 5, 2010 at 2:23 pm
Without knowing more detail about a lot of aspects of what you're doing and why you're doing it, it is hard to be very sure with advice, so take it for what it's worth.
One question I have is in regards to the groups. Are the groups just a mechanism to handle bulk invitations or is there something more to it? If it's just a bulk invitation, you don't need to do a while loop to handle the insertions one by one. You can just do something like:
INSERT INTO tableSessionInvites (SessionID, ClientID)
SELECT @SessionID, tgm.ClientID
FROM tableGroupMembers tgm
WHERE tgm.GroupID = @GroupID AND
tgm.ClientID NOT IN (SELECT ClientID from tableSessionInvites WHERE SessionID = @SessionID)
I personally don't see anything on this that needs to be handled by triggers. I would create the Session table with a SessionID PK that would be used as a FK in your other tables. You would handle knowing which session you're working with on the app side, so any insertion of groups of individuals to the invite list would reference that ID. Or, if you're building both the session and an invite list at the very same time and you don't want any app-side logic, you can insert the new session and use a variable to hold the SCOPE_IDENTITY() value to handle the invite insertions.
One other quick note ... anything that is specific to the particular SessionID/ClientID pairing I would keep in that same tableSessionInvites (or whatever you call it) table. So something like a boolean for whether or not they attended, or what time they arrived ... since there can only be one value for any pairing I would keep that together. If you have something like notification, where it's possible that multiple notifications would go out through various means or at various time, I would put that in a separate table with a FK linked back to the table with the pairing definition.
Does this help at all?
April 5, 2010 at 3:48 pm
I need to give a good re-think to my design
your query is very good it does the job well with selecting clients from groups the trigger that I created is too complex and it might hit performance that's why I have posted this topic cause I couldn't think of a different way of doing it
btw this is only for learning purposes
and it was a good lesson
KISS - keep it simple stupid
thanks for reminding me that
cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply