试图找出最好的数据库模式
我想提出一个通用模式(如果可能)用于我正在管理的许多不同事件。这些活动可以是婚礼、生日聚会等。
到目前为止,我有 3 个主要表格:
- 联系方式表 - 通常的信息,如地址、电话等。
- 活动表 - 一个包含日期、位置等信息的事件列表。
- EventInfo Table - 包含以下字段(不完整,但您应该明白要点):
EventID
联系方式
受邀成人人数
受邀儿童人数
已回复(是、否)
参加的成人人数
NumberofChildrenAttending
这是我正在尝试改进的表。我正在尝试找出捕获事件数据的最佳方法,以便跟踪成人和儿童的数据。
似乎很奇怪,我需要成人和儿童的这些重复字段,但我想不出任何其他方法。我不想将 NumberAdults
和 NumberofChildren
放入联系人表中,因为儿童数量不一定等于 numberofChildreninvited
(有时成人只是邀请)
您对如何清理此架构有任何想法吗?或者以上是我能得到的最好的想法吗?
注意:在联系人表中,家庭有一个条目(因为它有一个地址),因此不会为家庭中的每个人存储字段。
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.
So far I have 3 main tables:
- Contact Table - the usual info like address, phone, etc.
- Events Table - a list of events with some info like date, location, etc.
- EventInfo Table - contains the following fields (not complete but you should get the point):
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.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
以下是我根据提供的信息对数据库进行建模的方法:
EVENTS
INVITATIONS
CONTACTS
这不是一个好主意建立一个涵盖整个家庭的联系人模型。它使邀请和邀请变得更加容易。如果联系人代表一个人而不是一个家庭,则跟踪事物。毕竟,一个家庭可以有 0 到 18 个孩子,并且可能不包括重要的其他人。每个人,假设青少年和青少年上,将有唯一的联系信息(即:手机、工作号码、电子邮件等)。这也使得确定人数变得更加容易...
邀请表允许您总结邀请和邀请。确认:
只需加入“联系人”表即可确定年龄,然后能够对成人和成人之间的邀请进行子分类。孩子们。
FAMILIAL_RELATIONS
CONTACT_ID
RELATED_CONTACT_ID
RELATION_TYPE
(父母、孩子、阿姨/叔叔、表弟、黑羊等)使用此表可以汇总以获取家庭成员...
CONTACT_METHODS
CONTACT_ADDRESS_XREF
ADDRESSES
您会注意到我与
EVENTS
和ADDRESSES
建立了一对一的关系,同时支持一对多的联系地址。与人相比,位置将相对静态。通过这种格式,您可以轻松查看哪些活动地点很受欢迎,以便您将来可以使用这些信息获得更好的价格。关于同一家庭的地址:这就是为什么 ADDRESSES 是一个单独的表 - 您不需要为每个人重新输入它,只需关联到正确的地址记录即可。
Here's how I'd model the database based on the provided info:
EVENTS
INVITATIONS
CONTACTS
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:
Just need to join to CONTACTS table to determine age and then be able to subcategorize the invitations between adults & children.
FAMILIAL_RELATIONS
CONTACT_ID
RELATED_CONTACT_ID
RELATION_TYPE
(parent, child, aunt/uncle, cousin, blacksheep etc)Use this table to rollup to get household members...
CONTACT_METHODS
CONTACT_ADDRESS_XREF
ADDRESSES
You'll notice I made a one to one relationship with
EVENTS
andADDRESSES
, 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.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.
您需要跟踪单独的邀请和回复吗?
如果是这样,您可以有一个单独的邀请及其状态表。然后您可以从对该表的查询中获取计数。
如果您只是简单地跟踪计数,我可能会将 InvitationCount 的单独表标准化为成人、儿童或其他任何内容的鉴别器列。这避免了仅将两类邀请硬编码到您的架构中。也许将来你可能会有更多类别(例如顾客、客户、参与者、观察员、表演者、音乐家、捐赠者、荣誉会员......)
Do you need to track the indiviual invitations and responses?
If so you could have a separate table for Invitations and their statuses. Then you could obtain your counts from queries against that table.
If you are simply keeping a track of counts I might normalise to separate tables for InvitationCount with a discriminator column for Adult, Child or anything else. This avoids hard coding into your schema just two categories of invitation. Perhpas in the future you might have more categories (eg. Customers, Clients, Participants, Observers, Performers, Musicians, Donors, Honorary members ...)
在这种情况下,如果没有任何其他要求,我可能会建议采用与您已经提议的类似的路线。
冗余字段不是问题,因为它们跟踪的是有关
邀请
的独特事实,而不是联系人。我可能会为
Response
(包含出席人数,可能与受邀人数不同)或Attendee
表保留一个单独的表,但考虑到您的情况,这并不是真正必要的当前的要求。In that case, and absent any other requirements, I'd probably suggest a similar route to what you've already proposed.
The redundant fields aren't an issue, since they are tracking a unique fact about the
Invitation
, not the contact.I'd probably keep a separate table for the
Response
(with number attending, which may be different from number invited) or anAttendee
table, but it's not really necessary given your current requirements.