Multiple values in lookup fields

database-designnormalization

I know newer versions of MS Access allow multiple selections in lookup fields.

For example, if I'm collecting information on my students, I can have a field called Race with lookup values that include White, Black, Hispanic, Asian, etc. and then set that to allow multiple selection.

In term of db design, is it better to use this feature of Access…. OR to have a field for each Race category and then True/False values for each one? And maybe even have this in a separate table?

–edit

I'm asking my question about the table, not the form. I realize the front-end display is not identical to the back-end strucure. Is it better to have a bunch of true/false fields in the table OR just one lookup field that allows multiples? (knowing the form is the best place to define the actual lookup values)

Best Answer

For a single student, why would you have multiple selection for race? This for the situation you mentioned as example.

I'd see both options valid (lookup with multiple selection or radio buttons with true-false) depending on the situation you need to display. What you show in the front is not identical with what tables you have in the database. Do your tables to fit simple normalization rules and then just build queries and views to display in the Access UI. Read about normalization and normal forms and design your tables accordingly (I prefer by default 3rd normal form, but it's not mandatory, you could increase/decrease the form level as you see fit).

I think that this tutorial is done with Access users in mind.

You can find some good free data models here: Free data models. Some of them even have attached Access databases.

PS: more of a comment than an answer, but too verbose.

Related Solutions

MySQL table with multiple values in one field

You can keep one table for property and one master table for roles and them assign that role to a user in the user table and can keep one to many association between property and users by having separate table property_users

The best database design to keep track of fields with multiple values

Your new design constraint is such that there's a many-to-many relationship between pages and fields, and a many-to-many relationship between page-fields and field values. That means you'd have to do this:

Page
* Page_Id

Field
* Field_Id
* Field_Type_Id
* Field_Name

Page_Field
* Page_Field_ID
* Page_Id
* Field_Id

Page_Field_FieldValue
* Page_Field_FieldValue_ID
* Page_Field_ID
* FieldValue_Id

FieldValue
* FieldValue_Id
* FieldValue_Value
* Current_Value

"That's really gross" you're thinking. Yes, it is. I'd strongly suggest creating views (indexed or materialized if possible) to make retrieval easier. It doesn't really solve your problem of having to define everything for each new page, but at least you won't be storing the same data repeatedly.

The Field_Type table doesn't change at all. It's relation to Field is the same. I did not list it in order to reduce confusion.

I'd also caution about using the FieldValue.Current_Value field as you seem to be suggesting, as that will not really work if your application allows multiple users. Application state needs to be stored separately from application data, typically.

Best Answer

Related Solutions

MySQL table with multiple values in one field

The best database design to keep track of fields with multiple values

Related Question