Table Design - Yes/No or Text

[Shaun]

Hi Everyone,

I'm trying to figure out which would be the better design for a table or two.

I'm converting paper forms into a db format. On the paper forms, there are
categories for Current Land Use (Ag, Commercial, Residential, etc) and
Disturbance (Logged, Erosion, etc). Each of these have several choices that
are present/absent. Each can have multiple things checked.

I'm wondering if it would be better to store these as two separate tables
with a Key Field and a Description or would it be better to have a table with
10-12 fields of yes/no? Thanks!

Id CurrentUseDescription
# Text
Id DisturbanceDescription
# Text

OR

Id Agricultural Commercial Residential etc......
# Yes/No Yes/No Yes/No

 

[rpw]

This sounds like it should be a many-to-many relationship.

One piece of land might have many choices and any one choice might be
associated to many pieces of land. If this is true, then I suggest something
like this:

tblLand
LandID (pk)
LandDesc (text)

tblChoice
ChoiceID (pk)
ChoiceDesc (text)

tblLandChoices
LandChoiceID (pk)
LandID (fk)
ChoiceID (fk)

It may be that you don't need 'tblChoices' but you do need tblLandUse and
tblLandDisturbance....

 

[Pat Hartman]

You might even be able to use a single table if you add a type column. But
the ID, Description is the way to go. You will have nothing but trouble
going with yes/no columns in a single row.

 

[Shaun]

Nope, not a many to many. We're not storing any sort of attributes about the
land use/disturbance; only the fact that there is one or more associated with
a location.
While there is a lookup table in the db for the choices, this is just used
as the values that appear in the combo box on the form for data
entry/editing. These choices could be a value list rather than a table
lookup, but I wanted the choices to be easily edited by someone unfamiliar
with Access (much easier to add another thing at the end of a table then
going into table design and adding choices under the value list). Sorry if
the sample tables I put in the first post confused you, here's a better
description of them.

tblDisturbance (both fields together form the PK)
LocationID (fk)
DisturbanceType (text)

tblCurrentUse (both fields together form the PK)
LocationID (fk)
CurrentUseType(text)

But if I understand the gist of your post, you're saying doing it as the two
small tables rather than the one table with the massive list of Yes/No's is
the way to go.

A side note, this is not really a stand-alone db. The input for it is
coming from a PDA application that creates csv files that are then imported
into the various tables. So doing pk's as numbers isn't really possible as
the PDA does not have access to the various pk values, just the text
descriptions. Not to mention, having a drop down on the PDA with just a
bunch of numbers wouldn't do much good to the field crew.

Thanks again!

 

[Shaun]

Hi Pat,

Thanks for the tip. I knew there had to be a way to combine them, but have
been staring at this thing for so long, my head was stuck. The nice thing
is, that will actually simplify not only the db, but also the PDA side.

Thanks!

 

[rpw]

But if I understand the gist of your post, you're saying doing it as the two
small tables rather than the one table with the massive list of Yes/No's is
the way to go.

</snip>

Yes, that is correct - I am suggesting to avoid the massive list (fields) of
Yes/No's in one table.