试图找出最好的数据库模式 [英] trying to figure out the best database schema

问题描述

我想提出一个通用模式（如果可能的话）用于我正在管理的一些不同的事件。这些活动可以是婚礼，生日派对等。

I want to come up with a generic schema (if possible) to use for a number of different events that I am managing. These events can be weddings, birthday parties, etc.

到目前为止，我有3个主表：

So far I have 3 main tables:

1. 联系表 - 通常的信息，如地址，电话等。


2. 活动表事件与某些信息，如日期，位置等。


3. EventInfo表 - 包含以下字段（不完整，但您应该得到点）：

1. Contact Table - the usual info like address, phone, etc.
2. Events Table - a list of events with some info like date, location, etc.
3. EventInfo Table - contains the following fields (not complete but you should get the point):

EventID

ContactID

成交数目

Number ofChildrenInvited

回复（是，否）

成交数量

NumberofChildrenAttending

EventID
ContactID
NumberofAdultsInvited
NumberofChildrenInvited
Responded (yes, no)
NumberofAdultsAttending
NumberofChildrenAttending

这是我正在改进的表。我试图找出捕获事件数据的最佳方法，我们希望跟踪成人和儿童的数据。

This is the table that I'm trying to improve. I am trying to figure out the best way to capture the event data where we want to keep track of data across adults and children.

我觉得很奇怪，我需要这些重复成人和儿童的领域，但我不能想到任何其他方式。我不想在联系表中放入 NumberAdults 和 NumberofChildren ，因为孩子数不一定等于 numberofChildreninvited （有时候大人只是被邀请）

It seems strange that I need these repetitive fields for adults and children, but I can't think of any other way. I don't want to put NumberAdults and NumberofChildren in the contact table because number of children doesn't necessarily equal numberofChildreninvited (sometimes adults are just invited)

你有什么想法可以清理这个模式吗以上我可以得到最好的？

Do you have any ideas how I can clean up this schema or is the above the best that I can get?

注意在联系表中，有一个家庭条目（因为它有一个地址），所以没有一个家庭内每个人存储的字段。

NOTE: In the contact table, there is one entry for the family (as it has one address) so there are not fields stored per person within a family.

推荐答案

这是我将如何根据提供的数据库建模信息：

Here's how I'd model the database based on the provided info:

• EVENT_ID


• ADDRESS_ID

• CONTACT_ID


• EVENT_ID

• CONTACT_ID

将联系人建模成包含整个家庭不是个好主意。它使得更容易地邀请&如果联系人代表一个人而不是一个家庭，跟踪事情。毕竟，一个家庭可以有从0到〜18个孩子的任何地方，可能不包括重要的其他人。每个人，假设青少年&将具有独特的联系信息（IE：手机，工作号码，电子邮件等）。这也使得更容易确定人数...

It's not a good idea to model a contact to be encompassing an entire family. It makes it easier to invite & track things if a contact represents a person rather than a household. After all, a household can have anywhere from 0 to ~18 kids, and may not include a significant other. Each person, assuming teens & up, will have unique contact info (IE: cell phone(s), work numbers, email, etc). This also makes it easier to determine headcount...

邀请表允许您总结邀请和确认：

The invitations table allows you to summarize invitations & confirmations:

  SELECT e.event_name,
         SUM(invited.contact_id) 'total_invited',
         SUM(confirmed.contact_id) 'total_invitations_confirmed'
    FROM EVENT e
    JOIN INVITATIONS invited ON invited.event_id = e.event_id
    JOIN INVITATIONS confirmed ON confirmed.event_id = e.event_id
                            AND confirmed.responded = 'Y'
GROUP BY e.event_id, e.event_name

只需需要加入联系表来确定年龄，然后才能对成人和儿童之间的邀请进行子类分类。

Just need to join to CONTACTS table to determine age and then be able to subcategorize the invitations between adults & children.

• CONTACT_ID


• RELATED_CONTACT_ID


• RELATION_TYPE （父母，小孩，阿姨/叔叔，表弟，黑ep等）

• CONTACT_ID
• RELATED_CONTACT_ID
• RELATION_TYPE (parent, child, aunt/uncle, cousin, blacksheep etc)

使用此表进行汇总以获取家庭成员...

Use this table to rollup to get household members...

• CONTACT_ID


• METHOD_TYPE（手机，手机，手机，传真，电子邮件，即时消息等）


• METHOD_VALUE

• CONTACT_ID


• ADDRESS_ID


• ADDRESS_TYPE（家庭，商家等）

• ADDRESS_ID


• ADDRESS_1


• ADDRESS_2


• ADDRESS_3


• ADDRESS_4


• CITY


• PROV_STATE


• POSTAL_CODE


• COUNTRY

• ADDRESS_ID
• ADDRESS_1
• ADDRESS_2
• ADDRESS_3
• ADDRESS_4
• CITY
• PROV_STATE
• POSTAL_CODE
• COUNTRY

你会注意到我做了一对一的关系与 EVENTS 和 ADDRESSES ，同时支持一对多联系人到地址。与人相比，地理位置相对较为静态。这种格式允许您轻松查看哪些活动地点受欢迎，因此您可以使用这些信息以获得更高的利率。

You'll notice I made a one to one relationship with EVENTS and ADDRESSES, while supporting one-to-many contact to addresses. Locations will be relatively static, compared to people. This format would allow you to easily check which event locations are popular, so you could use the information to get better rates in the future.

关于同一住户的地址：这就是为什么ADDRESSES是一个单独的表 - 您不需要为每个人重新输入，只需将其与正确的地址记录相关联。

Regarding addresses for the same household: That's why the ADDRESSES is a separate table - you don't need to retype it for each person, just associate to the correct address record.

这篇关于试图找出最好的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！