Design question: how to use a table to indicate "good" records from another table

[jfhspike]

Design question: how to use a table to indicate "good" records from another table

I need some help with database design/access implementation. Here's the simplified question:

Main table ("Person"):
ID <whcih is also the "key">
Name
Address
[lots of other fields]

I'd like to print out the name and address for SOME of these records. To be exact, I'd like to offer the user a list showing

name1 checkbox1
name2 checkbox2
name3 checkbox3
...

and so on. The user can then click some of the check-boxes, and later I'll use those to select the records to print.

The easy way would be to add a "chooseMe" field in the main table, but that seems wrong: 'chooseMe' is not a property of a person the way the name or address is. So I'd like to make a related table. (That'll also make it easier for me to "clear all selections" and things like that, I believe).

I see two main choices.

1: The "selector" table has one row for each row of the Person table, i.e., it has an "ID" and a "select" field (Yes/No).

2. The "selector" table has one row for each person I want to select; that row simply contains the ID of the person. Anyone whose ID isn't in the selector table is "not selected".

Choice "2" seems preferable to me, but I'm not at all certain how to get an interface that lists all the people, each with a checkbox, even though some of them have no "related" record inthe "selector" table. And I don't know how to make a record "appear" in the "selector" table when the user checks the checkbox, and disappear when s/he unchecks it. I assume it's something to do with "cascade related tables" and "ensure referential integrity," but frankly, I don't know exactly WHAT.

If someone can help out with a bit of theory and practice here, I'd be enormously grateful.

John

 

[pbaldy]

Re: Design question: how to use a table to indicate "good" records from another table

From what you describe, I wouldn't have another table at all. I'd use this:

http://www.baldyweb.com/multiselect.htm

 

[jfhspike]

Re: Design question: how to use a table to indicate "good" records from another table

From what you describe, I wouldn't have another table at all. I'd use this:

http://www.baldyweb.com/multiselect.htm

Nice suggestion, but not, in fact, appropriate in my context. In truth, the user will be messing with about 800 records, and may, while working on a record, say "THAT's one that I have to print later", and click a checkbox on a form showing this particular record. Then, just prior to printing, s/he may say "let me look at the whole list of people and see if there are a few others I want to select as well, and then I can print them all." It's not reasonable to expect the use to remember which seventy people needed printing over the course of a couple of days of interaction, and then multi-select them all later.

I'll definitely use your suggestion in a couple of OTHER places, though!

Thanks!
-John

 

[pbaldy]

Re: Design question: how to use a table to indicate "good" records from another table

Sounds reasonable. I might take the easy way out and just put a field in the main table, though if you're in a multiuser situation that wouldn't work anyway (presuming different users would choose different records). If you used option 2, which also makes more sense to me, you could have code in two places. First in the current event, to check or uncheck the box based on whether the current person appears in that table. Second in the after update event of the checkbox, to add/delete them as appropriate to the selection. I suppose the advantage to option 1 would be that you might be able to bind the form to a query that joined both tables, which would eliminate the code, but only if the query remained editable.