ASP.NET 3.5 的半结构化数据库设置
对于小型“联系人管理”类型网站来说，最佳的数据库设置是什么？它将仅在内部使用（Intranet），我使用的是 ASP.NET 3.5 和 SQL Server。
该数据库存储有关我们接触的人、他们工作的公司等的信息。许多人可以为同一家公司工作 (n:1)，但人们也可能为多家公司工作 (n:m)。人们可以拥有多个电子邮件地址和电话号码。公司可以在不同地点设有多个办事处等。数据库设计必须足够灵活，以便稍后添加新字段。
我知道有几种不同的方法，但我不知道哪种方法最好。
- 非常经典，针对不同主题（人、公司）的不同表，有很多字段，如“email1”、“email2”，以及当可能存在 n:m 关系时关联主题（哪些人在哪些公司工作）的表。稍后可以毫无问题地添加其他字段（“email3”）。将会有许多空白字段，因为您必须考虑任何人可能拥有的电子邮件地址的最大数量。
- 单独的表格用于存储电子邮件地址等内容，链接回人员表。如果（例如）原始设计只需要一个电话号码，而后来决定在单独的表中处理电子邮件地址等电话号码，则可能会出现问题。最终可能会有很多不同的桌子。但设计相对节省空间（几乎没有空字段）。
- 邻接列表模型，其中每个属性在表中都有自己的记录，以及指向主记录的 ID 指针。例如，姓名、地址、所有电子邮件地址、所有电话号码等都存储为指向同一主要个人记录的单独记录。数据库根本不会有空字段，并且以后可以自由地添加其他结构。
- 所有数据都以 XML 形式存储在 SQL Server 数据库中（使用 XML 数据类型）？
- 或者只是纯 XML 文件？
What would be the best database setup for a small "contact management" type website? It is going to be used internally only (intranet), and I'm using ASP.NET 3.5 and SQL Server.
The database stores information about people we are in contact with, companies they work for, etc. Many people can work for the same company (n:1), but people might also work for several companies (n:m). People can have multiple email addresses and telephone numbers. Companies can have multiple offices at different locations, etc. The database design has to be flexible enough to add new fields at a later point.
I know of several different approaches, but I don't know which will be the best.
- Very classic, different table for different subjects (people, companies), with lots of fields like "email1", "email2", and tables to relate the subjects (what people work in what companies) when there can be n:m relationships. Additional fields ("email3") can be added later on without problem. Will have many empty fields since you have to take into account the maximum number of email addresses any person may have.
- Separate tables for things like email addresses, that link back to the people table. Can be problematic if (for example) the original design only requires one telephone number, and at a later point it is decided to handle telephone numbers like email addresses in a separate table. May end up with lots of different tables. But the design is relatively space-efficient (few empty fields).
- Adjacency list model, where every property has it's own record in the table, and an ID pointer to the main record. For example, name, address, all email addresses, all telephone numbers, etc are stored as separate records pointing to the same main personal record. The database won't have empty fields at all, and there is lots of freedom for additional structures later on.
- All data stored as XML in the SQL Server database (using the XML data type)?
- Or just plain XML files?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议使用关系数据库。您的项目看起来很简单，只需拥有具有正确关系的正确表即可解决。我会远离 XML，因为数据会保持一致。 XML 更适合具有不可预测结构的数据，例如家谱。数据库将性能更好并且更容易维护。与 XLM 格式相比，RDB 格式的数据报告也更容易。
在编码时考虑到可扩展性是一个很好的做法。如果您正在管理联系人，我可以向您保证他们在某些时候会需要多部电话。
当属性数量不断增加但结构简单时，邻接列表模型更适合。例如，在一个配置文件中，您有 n 个问题和答案，但它们都与一个用户相关。
I would suggest a relational database. your project seems simple enough to be addressed by just having the right tables with the right relationships. I would stay away from XML since the data is going to be consistent. XML is better for data with unpredictable structures, like for example a family tree. A database will perform better and it will be easier to maintain. It's also easier to report on data when is in a RDB as opposed to XLM format.
It’s good practice to code with scalability in mind. If you are managing contacts I can guarantee you they will need multiple phones at some point.
Adjacency list model is better for when you have a growing number of properties but the structure is simple. For example a profile where you have n number of questions and answers but they all tie back up to one user.