Multiple tables or one tables for multiple drop-downs

[hilian]

Our data entry process requires multiple drop-downs with multiple entries for a single case (one-to-many relationship). The multiple-entry fields are scattered through the form. I've created a separate, related table with a subform for each of the multiple-entry fields.

My question is, is this a good practice? The alternative is to put all of the multiple entry fields into a single table. I need a separate sub-form for each of the fields because they're scattered through the data-entry process. (They can't be grouped together because they each relate to a separate question.) Having a separate table means that a new record would be created each time a new multiple-entry field required an entry, and the result would be a multitude of records with many fields but entries in only one field. Neither way seems like an efficient use of tables.

Which is correct?

Thanks,

Henry

 

[jdraw]

Not sure I understand your specifics, but this is based on my interpretation
see one lookup table or several see this for info

 

[hilian]

That thread was very interesting. I hadn't thought of having one field for entry type and another for entries. I had thought of the table as having a case ID number to relate to to the main table and separate fields for each question on the paper data-entry sheet. That would have left each record having data in the case id field and one other field, and all other fields would be empty.

In the example, each attribute has only one possible answer, so I'm not sure why separate tables are needed at all. In my situation, each question (the equivalent of attribute in the example) can have several answers, e.g a particular case can have domestic violence (plus) substance abuse (plus) homelessness, etc., so there would be several records for each case for each question that applies. I'm not sure that makes a difference in the way the table is structured. Does it? The fields would be populated from separate drop-downs for each question. That shouldn't affect the table structure, should it?

Also, I'm not sure I understand how I would structure a query to put all the information for each case together. would it be a query with one field for case ID and separate fields with expressions for each question something like: attribute ID = the attribute I want. woud that work?

There seems to be a difference of opinion as to one table is a good idea. What's your take on this?

Many thanks,

Henry

 

[jdraw]

I like individual tables.
Here's Joe Celko's view.

 

[hilian]

Thanks for the article. It was a challenge reading it while I'm under a deadline, but I get the point.

Is there a particular way of handling a very large number of tables, about 30, to avoid confusion? Naming conventions are helpful, but there is still a clutter of tables.