会员列表最佳数据库建设实践
我想为我的协会建立一个会员数据库。我们是一个由较小的地方实体组成的全国性组织。
我想知道哪种做法会更好地以速度和开销的方式组织我的数据库：
- 创建一个包含所有成员的大表，并使用“本地关联”字段进行过滤。
- 创建许多表，每组一个。
（顺便说一句，我正在 joomla 上工作，如果这可以改变一些东西）
编辑：我会让协会的结构更清晰。一名会员一次只能加入一个小型当地协会。所有会员都是国家协会的成员。对于每个成员（以及每个协会），数据字段都是相同的。如果还不清楚请评论我:)
I want to make a members database for my association. We are a national organization that is composed by smaller local entities.
I was wondering which practice would be better to organize my database in the mean of speed and overhead:
- Create one large table with all the members with a "local association" field for filtering.
- Create many tables, one for each group.
(btw I'm working on joomla, if this could change something)
EDIT: I'll make clearer the structure of the association. One member can be part of only one small local association at time. All member are part of the national association. For every member (and so every associations) the data fields are the same. Comment me if it's still not clear :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
由于您发布的要求有限，这就是我的看法。这是第三种选择，因为您发布的两个选项确实不适合数据库。
您描述了 3 个不同的实体（成员、组（关联）、地址（位置））和每个规则为了实现标准化，您需要将它们适当地分开。如果您仅将所有内容保留在 1 个或 2 个表中，则整个数据库中都会有重复项。就我个人而言，我同意托马斯的观点，并会考虑合并我发布的第二个选项。
我只是给出了一个基本的表结构，同样不理解您的全部要求，因此请根据您的要求调整关系基数。我试图强调的答案中最重要的部分是适当地正常化。与其他人所说的简单性相反，没有比正确设置数据库并正确规范化更简单的了。
With the limited requirements that you have posted this is how I see it. It is a third alternative because the 2 options you posted really are not appropriate for a DB.
You describe 3 different entities (Member, Group (Assoc.), Address (Location)) and per rules of normalization you need to separate those appropriately. If you keep everything in only 1 or 2 tables you will have duplicates throughout your DB. Personally I agree with Thomas here and would look into incorporating the second option that I posted.
I just gave a basic table structure, again not understanding your full requirements, so please adjust the Relationship Cardinality as you see fit for your requirements. The most important part of my answer that I am trying to stress is normalize appropriately. Contrary to what others say about simplicity you can't get any more simple then having a properly set up database that in turn is properly normalized.
假设您正在跟踪所有成员的相同字段，无论当地协会如何，我都会倾向于为所有成员提供一张表。当您添加/删除本地关联时，这将显着降低您的维护开销。
如果不同的当地协会跟踪有关成员的显着不同的信息，那么您可能需要稍微重新考虑此建议。
Assuming that you are tracking the same fields for all members, regardless of local association, I would lean towards one table for all members. This will significantly lower your maintenance overhead when you add/remove local associations.
If different local associations track significantly different information about members, then you may need to reconsider this recommendation a bit.
我会考虑第三种选择：成员表、关联表以及将两者关联在一起的表。通过这种方式，同一个人可以成为多个协会的一部分，而不必重复他们的信息。
I would consider a third alternative: a table of members, a table of associations and a table that associates the two together. In this way, the same person can be part of multiple associations without having to duplicate their information.
我会建议一张大桌子。即使理论上将其拆分更快，您也会失去很多简单性方面的优势。例如，就速度而言，请考虑针对一张表（而不是 10 个表）调整和优化查询的简单性。
我还担心如果您有多个表，则会有很多冗余。因此，无论如何，如果您进行拆分，请尽量减少冗余并使用关系来避免维护大量几乎相同的表。
I would suggest one large table. Even if splitting it up were theoretically faster, you'd lose a lot of advantages in terms of simplicity. For instance, in relation to speed, consider the simplicity of tuning and optimizing queries against one table as opposed to 10 tables.
Also I'm concerned that if you have multiple tables, you would be having a lot of redundancy. So by all means, if you do split, try to minimize redundancy and use relationships to avoid having to maintain tons of almost identical tables.