If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design - Yes/No or Text
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 |
#2
|
|||
|
|||
Table Design - Yes/No or Text
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.... -- rpw "Shaun" wrote: 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 |
#3
|
|||
|
|||
Table Design - Yes/No or Text
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" wrote in message ... 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 |
#4
|
|||
|
|||
Table Design - Yes/No or Text
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! "rpw" wrote: 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.... -- rpw "Shaun" wrote: 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 |
#5
|
|||
|
|||
Table Design - Yes/No or Text
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! "Pat Hartman" wrote: 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" wrote in message ... 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 |
#6
|
|||
|
|||
Table Design - Yes/No or Text
snip
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. |
Thread Tools | |
Display Modes | |
|
|