Many to Many

[gjeih]

Hello.

This is probably quite simple but I have ODed on databases the last couple of days and I can no longer think straight!

So I have clients, and the clients attend sessions, two tables, many to many I guess. When a new session is created, the session number increments. However, I would like each client's session to have an identifier to show which session number that individual is on.

If I understand correctly, I need an intermediate table called clientsession. Is this called resolving? And if so, what are the various PKs and FKs?

Many thanks

Gerard

[David Kachel]

If I understand correctly, it does not sound like you need a Many to Many.

One client can attend many sessions (one to many).

One session can have many clients (one to many).

Have a portal in the clients table that shows the sessions for that client.

And have a portal in the sessions table that shows the clients for that session.

Does this make sense, or have I completely misunderstood your question?

[gjeih]

Thanks David

No I'm not sure if I've explained correctly.

I have a table for each client showing all their sessions.

I want each session to be numbered sequentially per client. Currently with each new session created (for any given client), the session_id increments, so client A might have sessions 1, 3, 8 etc. I would like sessions to be numbered like this

client A session 1 (session_id 1)

client B session 1 (session_id 2)

client C session 1 (session_id 3)

client B session 2 (session_id 4)

client A session 2 (session_id 5)

client B session 3 (session_id 6)

I know it should be simple but I still get slightly confused with keys and joins. Is a join required to allocate individuals session_ids?

cheers

Gerard

[comment]

If I understand correctly, your problem can be resolved very simply by going into Layout mode and typing @@ in the portal showing a client's sessions.

Or you could count a client's session by defining a calculation field in the Clients table = Count ( Sessions::session_id ).

Unless there can be more than one client per session, this is not a many-to-many situation. In any case, it's best not to monkey around with the ID numbers. There's no need to even show them in the portal.

[David Kachel]

Are you viewing the session in a portal (I presume)?

Do you want the sessions numbered sequentially from top to bottom, i.e., portal row 1 is always session 1, row 5 is always session 5, etc., or wiil some be random?

[gjeih]

Thanks for the replies, I think the calculation field is a good idea.

David, I do want the sessions numbered sequentially (which should also be in date order) descending.

Cheers

Gerard