在 MS Access 中创建查找字段 - 最好有一个或多个源表? [英] Creating lookup field in MS Access - better to have one or many source tables?

问题描述

我正在创建一个数据库来跟踪我们在 Access 2010 中的参与者.

I'm creating a database to track our participants in Access 2010.

主要数据存储在名为 ParticipantMaster 的表中.

Main data is stored in a table called ParticipantMaster.

我想为 ParticipantMaster 分配查找值的几个字段.

I have a few fields in ParticipantMaster that I want to assign lookup values to.

学校(MLK 高中、中央高中、东北高中...)

School (MLK High, Central High, Northeast High...)

兴趣(视觉艺术、戏剧、体育、文学......)

Interests (Visual Arts, Drama, Sports, Literature....)

颜色(红、蓝、绿...)

Color (Red, Blue, Green...)

我可以创建一个表 School、另一个表 Interests、另一个表 Color，然后将每个表的源设为 SELECT Color.ColorName FROM Color，然后是 SELECT School.SchoolNameFROM School 然后SELECT Interests.InterestPicker from Interest....这就是我通常做事的方式.

I could create a table School, another table Interests, another table Color, and then have the source for each one to be SELECT Color.ColorName FROM Color and then SELECT School.SchoolName FROM School and then SELECT Interests.InterestPicker from Interest.... this is how I usually do things.

但后来我想，如果不是三个单独的表，我可以有一个名为 ParticipantData 的表，其中包含名为 School、Interests、Color... 的字段，然后我的查找查询是 SELECT ParticipantData.School from ParticipantData 和 SELECT ParticipantData.Interests FROM ParticipantData 和 SELECT ParticipantData.Color FROM ParticipantData

But then I wondered if instead of three separate tables, I could have one table called ParticipantData with fields called School, Interests, Color... and then have my lookup queries be SELECT ParticipantData.School from ParticipantData, and SELECT ParticipantData.Interests FROM ParticipantData, and SELECT ParticipantData.Color FROM ParticipantData

一种方式或另一种方式有优点/缺点吗?

Are there advantages/disadvantages to one way or the other?

推荐答案

您描述的查找表有点独特.大多数人不会建议你这样做.

The lookup table you described was a bit unique. Most people wouldn't suggest you do this.

    ParticipantData
 +-----------------------+
 | School    (text)      |
 | Color     (text)      |
 | Interests (text)      |
 +-----------------------+

下面那个更有意义

    ParticipantData
 +-----------------------+
 | ID (AutoNumber)       |
 |-----------------------|
 | LookupType (number)   |
 | LookupValue (text)    |
 +-----------------------+

拥有这样一张桌子的主要缺点是

the major downsides to having a single table like this are

1. 让外键约束变得完美真的很难.例如您想将 ParicipantMaster.Color 限制为仅对应于 LookupType 颜色的 ID，但您能做的最好的事情是将其限制为 Participant Data 表中的 ID.

1. Its really hard to make the Foreign Key constraints perfect. e.g. You want to limit ParicipantMaster.Color to just IDs that correspond to the LookupType color but the best you can do is limits it to IDs that are in the Participant Data table.

如果您只想将属性添加到一个 LookupType，这会变得很痛苦.例如，您想将 SchoolDistrict 添加到学校的 LookupType 中，您有一堆不受欢迎的选择.(A. 允许 schoolDistrict 成为颜色属性.B. 从查找表中取出 School 并修复所有代码，C. 添加一个链接到参与者表的表)

It becomes painful if you want to add attributes to just one LookupType. For example you want to add SchoolDistrict to the LookupType of school you have a bunch of undesirable choices. (A. Allow schoolDistrict to be an attibute of color. B. Rip out the School from the lookup table and fix all your code, C. Add a table that links to participant table)

这篇关于在 MS Access 中创建查找字段 - 最好有一个或多个源表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！