Re: Need help with database/forms design, please...

I assume that you have a table "BLAH" with field "Affected CPCI"

AFAIK you have two solutions:

1. Alter table "BLAH" to have 5 fields instead of 1. So you have one field for every type of problem. When your problem report affects for example software and hardware then you insert values into fields blah.software and blah.hardware, other 3 fields have null values. IMHO this is stupid solution, because when you need to add a CPCI (???), you have to alter your table. Anyway, this makes querying easyier and possibly faster.
2. Use 3 tables instead of 1. Those 3 tables would be "BLAH", "CPCI" (whatever this means :) and "AFFECTION" (Sorry, my English is not very fluent, so maybe those names are nonsense). New "BLAH" is similar to old "BLAH" but it does not have field "Affected CPCI". New "BLAH" must have a field, which uniquely identifies every record in this table (primary key). For instance: "BLAH.ID". Table "CPCI" has fields "ID" (primary key) and "DESCRIPTION". This table should have following records:

ID      DESCRIPTION
---     -----------
1       Software
2       Hardware
3       Documentation
4       Work Flow
5       Other

The third table "AFFECTION" is connection between tables "BLAH" and "CPCI" using relations. This table must have fields "BLAH_ID" (foreign key pointing to "BLAH.ID") and "CPCI_ID" (FK pointing to "CPCI.ID"). This table should have a composite primary key using fields "BLAH_ID" and "CPCI_ID".

Juhan

Richard Hollingsworth wrote:
>
> Hi folks. Thanks for reading this.
>
> I need your help with a design issue associated with checknoxes.
>
> I have a db (8.5) that currently has a field called "Affected CPCI". It
> is a text field, 30 chars long.
>
> On the form (2.1) I have 5 checkboxes under this label. They are...
>
> 1. Software
> 2. Hardware
> 3. Documentation
> 4. Work Flow
> 5. Other
>
> Here's the problem. This field "Affected CPCI" needs to be capable of
> accepting multiple choices. That's why I chose checkboxes. For
> example, the problem report could affect both Software and
> Documentation.
>
> How do I define this field to accept multiple choices? I can (and
> currently do) just append the choices together in the field on say a
> form exit trigger. This puts all choices in the field seperated by
> commas, but makes it hard to do querries on the field.
>
> What would yo suggest to be the best method for defining this field and
> the form inputs so that the field will handle multiple choices and the
> querry process will be simple?
>
> Thanks for your input....
>
> Richard H.
Received on Mon Feb 28 2000 - 10:56:59 CST