I have a data file called Members and each Member has a Unique Keyed field (CamNum). I have another file (Sessions) with multiple fields in it that get stuffed with these unique Member numbers.
Files
Members: CamNum, First, Last, etc (CamNum is unique ID)
Sessions: Slot1, Slot2, Slot3, etc - Each of these Slot fields gets filled the Mem:CamNum
A window in the app displays Members (text) Names in time Slots by doing a lookup to the Members file by setting the MEM:CamNum = SES:Slot1 and doing the lookup.
Question: Is there a way to skip the lookup and use a relationship MEM to SES? I don’t see how to do it since the SES file contains multiple individual fields that each contain CamNums.
Bad design? Maybe SES should contain these fields: Slot, CamNum
Yes, I think I would tag that as bad design. For example, if you wanted to know if Bob was in Session1, you’d have to ask is he in Session 1 Slot 1, Session 1 Slot 2, Session 1 Slot 3 and so on.
Also, the Clarion way of dealing with a design like that require a whole bunch of alias files, one for each of your slots, so that when you look up the person in slot 2 you don’t lose the person you looked up for slot 1. Messy, even before you get to reporting.
IMO the way to do it is like you suggested: you have a many-many link table, just links your members with a session. Your session table gives the time of the session and whatever, but not info on the people assigned to that session.
If you are using a database backend (not ISAM files) then a listagg is probably the easiest way of showing all the people currently assigned to a particular session.
I’m with Jon, For what you described I think you’re at 2nd normal form. I’d split the table to give Session and another for Members in that Session. You end up with more tables, but it actually usually ends up way easier to work with.
Note that the way info is stored in the DB and the way you use it in your program can differ. I have loaded a child table into an array to process for example.
Like Sean says I would split up table Sessions into two tables: SessionHeaders and SessionSlots
Table SessionHeaders has fields like SessionID and …
Table SessionSlots has fields like: SlotID, SessionID (Foreign Key), SlotNo, CamNum.