Need help with table design/work flow

[gbraden]

Hi all,

I am not very good with creating databases and how they need to work to get what I need. I tend to over-do it and make things far more complicated. I don't need code, I need to get a picture in my head how to hook up several elements.

Of course, this is needed yesterday, which I cannot do too much about.

Out of multiple tables:
Member table (member ID, colta, coltb, ...)
Tasking table (TaskID, Task, no of Seats, seatsID, ...)
Seating table (SeatsID, colsa, colsb...).

There a multiple tasks in the tasking table, each task has a number of seats assigned to it.

A member can have more than one seat, but each seat can only have one member.

1) I need to be able to pull up the tasks a member is assigned.
2) I need to pull up seats in a task and who is assigned them.
3) I need to find tasks that have empty seats.

I figure I need a join table between member and task, and maybe a join table where I can determine no of seats minus seats assigned.

Is this clear? I would include the mess I have on the whiteboard, but I didn't bring a camera today, which is the normal way I share whiteboard jottings.

Thanks in advance.


Glen Braden

 

[gbraden]

Correction to the problem.

A Member can have more than one TASK, The task can have more than one seat, but the member can only be assigned one seat from any giving task.


Glen Braden

 

[PHV]

Have a look here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gbraden]

OKAY, as I see it (after viewing the tutorial), I need to connect Member table to a new table (table assignment) where memberID is linked to taskID from the Tasking table.

The seat table contains a seatID and other information (such as seat usage). The Task table is connected to the seat table using seatID. The task table also has a number of seats (or members needed) for a given task.

To solve #3, I would need to do a count(*) grouped by taskID and with those results, select taskid, no of seats, and a caluclated column (vacant seats (results - no of seats)) grouped by taskid. Is that how you would solve this?


Glen Braden

 

[gbraden]

okay, solution for #3 (in psuedo code) would be to add column VacantSeats to the tblTasking. VacantSeats is a calculated field where [no of seats - (select count(taskID) from table Assignment)]

Thanks PH for the help!


Glen Braden

 

[jedraw]

PHV gave a very good reference.
You should seriously work through some Normalization tutorials, and investigate Data Models and junction tables.

There are a number of great references given by
user strive4peace2010 in a response at

http://www.utteraccess.com/forum/Update-field-t1946317.html&hl=normalization