CRM中会员实体重新设计的建议
我们有一个 CRM，其中包含一个会员实体作为系统中最重要的实体。问题是它有太多的属性，这使得它不规范。这是属性：
[MEMBER ID]
,[FIRST NAME]
,[LAST NAME],[TITLE],[ADDRESS 1],[ADDRESS 2]
,[ADDRESS 3],[POST CODE],[TELEPHONE HOME]
,[TELEPHONE WORK],[GENDER],[DURATION OF MEMBERSHIP],[START DATE]
,[AMOUNT PAID],[BALANCE],[STATUS],[DOB]
,[MONTH FEE],[ORIGINAL START DATE],[PAYMENT TYPE]
,[HEAR],[Interest],[NUMBER MONTH FEES]
,[FIRST MF DUE DATE],[LAST VISIT],[CARD NUMBER]
,[BANK NAME],[SORT CODE],[ACCOUNT NUMBER]
,[DEFINE1],[DEFINE2],[DEFINE3],[DEFINE4]
,[DEFINE5],[DEFINE6],[DEFINE7],[DEFINE8],[DEPENDENT]
,[ROLL NO],[ALLOWED VISITS],[TOTAL VISITS],[CREDIT LIMIT]
,[JOINING FEE],[NON VAT MONTH FEE],[PAYMENT METHOD]
,[CentreId],[Letter Title],[Email Address]
,[Vehicle Registration],[Standing Order Reference],[Notes]
,[Outstanding Balance],[MobileNo],[FaxNo],[Nonparent Password]
,[Emergency Name1],[Emergency Relation1],[Emergency HomeTel1],[Emergency WorkTel1],[Emergency MobileNo1]
,[Emergency Name2],[Emergency Relation2],[Emergency HomeTel2]
,[Emergency WorkTel2],[Emergency MobileNo2],[Doctors Name],[Doctors Tel],[Medical Info]
,[Password],[MethodOfContact],[Address 4],[Address 5]
,[Address 6],[ExtRef1],[ExtRef2],[ExtRef3],[ExtRef4],[OnMailingList],[HasChildren]
,[ParentMemberId],[MedicalIllness],[MedicalQuestion],[COMMENTS]
,[MembershipFeePaid],[JoiningFeePaid],[IsDeleted]
,[Pending],[Induction],[UserName]
,[CompanyName],[RowVer],[MembershipProductId]
,[Id],[EmailVerified],[ConcessionTypeId]
,[MemberTypeId],[Age],[Renewal_Date]
我正在考虑将这个东西标准化。有什么建议吗？
We have a CRM which contains a memebr entity as the most important entity in the system. The thing is that it has too many attribute in it which makes it unnormalized. here are the attributes:
[MEMBER ID]
,[FIRST NAME]
,[LAST NAME],[TITLE],[ADDRESS 1],[ADDRESS 2]
,[ADDRESS 3],[POST CODE],[TELEPHONE HOME]
,[TELEPHONE WORK],[GENDER],[DURATION OF MEMBERSHIP],[START DATE]
,[AMOUNT PAID],[BALANCE],[STATUS],[DOB]
,[MONTH FEE],[ORIGINAL START DATE],[PAYMENT TYPE]
,[HEAR],[Interest],[NUMBER MONTH FEES]
,[FIRST MF DUE DATE],[LAST VISIT],[CARD NUMBER]
,[BANK NAME],[SORT CODE],[ACCOUNT NUMBER]
,[DEFINE1],[DEFINE2],[DEFINE3],[DEFINE4]
,[DEFINE5],[DEFINE6],[DEFINE7],[DEFINE8],[DEPENDENT]
,[ROLL NO],[ALLOWED VISITS],[TOTAL VISITS],[CREDIT LIMIT]
,[JOINING FEE],[NON VAT MONTH FEE],[PAYMENT METHOD]
,[CentreId],[Letter Title],[Email Address]
,[Vehicle Registration],[Standing Order Reference],[Notes]
,[Outstanding Balance],[MobileNo],[FaxNo],[Nonparent Password]
,[Emergency Name1],[Emergency Relation1],[Emergency HomeTel1],[Emergency WorkTel1],[Emergency MobileNo1]
,[Emergency Name2],[Emergency Relation2],[Emergency HomeTel2]
,[Emergency WorkTel2],[Emergency MobileNo2],[Doctors Name],[Doctors Tel],[Medical Info]
,[Password],[MethodOfContact],[Address 4],[Address 5]
,[Address 6],[ExtRef1],[ExtRef2],[ExtRef3],[ExtRef4],[OnMailingList],[HasChildren]
,[ParentMemberId],[MedicalIllness],[MedicalQuestion],[COMMENTS]
,[MembershipFeePaid],[JoiningFeePaid],[IsDeleted]
,[Pending],[Induction],[UserName]
,[CompanyName],[RowVer],[MembershipProductId]
,[Id],[EmailVerified],[ConcessionTypeId]
,[MemberTypeId],[Age],[Renewal_Date]
i was thinking about normalizing this thing. Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
数据库重构通常是一个相当复杂的过程。看看您是否可以获得 SQL Refactor 红门。
重构的一种方法是创建一个模仿当前表结构的视图。一旦外部应用程序从视图而不是表中读取数据，您就可以开始重构表（然后您只需修改视图，而不是应用程序）。当然，这对插入或更新数据没有帮助，那是另一个故事了:)
Database refactoring is often a pretty paintful process. See if you can get a license for SQL Refactor by RedGate.
One approach to refactoring is to create a view which mimics the current table structure. Once external applications are reading from the view instead of the table, you can begin refactoring the tables (and then you only have to modify the view, not the applications). Of course this doesn't help with inserting or updating data, that's another story :)
首先，如果该字段已编号，则它通常是标准化的候选者。考虑将地址详细信息移出一开始。电话号码可以在其他地方并与类型一起存储，以避免需要有这么多可能不会使用的字段。如果为类型指定了序列，则地址详细信息可以遵循类似的模式（例如，您可以导出字段应打印的顺序）。
银行详细信息是另一个候选者。
这样考虑吧。成员应仅包含与该成员直接相关的详细信息。不是会员的银行、地址等。它应该包含会员的名字、姓氏、直接属性等详细信息。
考虑查看其中一些链接以获取想法：
http://databases.about.com /od/specicproducts/a/normalization.htm
http://support.microsoft.com/?id=209534
To start, if the field is numbered it is often a candidate for normalisation. Consider moving the address details out for starts. The phone numbers can be elsewhere and stored with types to save the need of having so many fields that are likely not used. Address details can follow a similar pattern if the types are given a sequence (so you can derive the order the fields should be printed for example).
Bank details are another candidate.
Consider it this way. The member should contain details that are directly relevant to the member only. Not the member's bank, address, etc. It should contain details of their first name, last name, direct attributes of the member.
Consider reviewing some of these links for an idea:
http://databases.about.com/od/specificproducts/a/normalization.htm
http://support.microsoft.com/?id=209534
http://ipconflict.co.uk/2009/12/29/basic-guide-to-database-normalisation-first-normal-form/