Group and individual client SQL SERVER DESIGN

  • 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

  • Can you give some examples of questions you're trying to answer with your database?

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • 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

  • 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?

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • 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