在 SQL 数据库中存储 DNS 域的最佳实践？
好的，所以我已经思考这个问题有一段时间了，这并不明显 - 这是我在 SQL 中的尝试（对于 PostgreSQL）：
CREATE TABLE domains (
id SERIAL NOT NULL,
display_name varchar(255) NOT NULL,
domain_name varchar(255) NOT NULL UNIQUE,
tld varchar(10) NOT NULL,
domain_type INT NOT NULL,
--
PRIMARY KEY (id)
) ;
但有些问题是： * 将 http:// - 与域名一起存储？ - 例如 http://www.domain.com ？ * 是否值得将 tld 分成它自己的列？例如.com * 案件？这有关系吗 * 还要别的吗？
其他人都做什么？ - 谢谢，
ok, so I have been pondering this a while now, and it is not obvious - here is my attempt in SQL (for PostgreSQL):
CREATE TABLE domains (
id SERIAL NOT NULL,
display_name varchar(255) NOT NULL,
domain_name varchar(255) NOT NULL UNIQUE,
tld varchar(10) NOT NULL,
domain_type INT NOT NULL,
--
PRIMARY KEY (id)
) ;
but some questions would be:
* store the http:// - with the domain? - e.g. http://www.domain.com ?
* is it worth to break out the tld into it's own column? e.g. .com
* case? does this matter
* anything else?
What does everyone else do? - thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
简而言之，没有存储 DNS 条目的最佳实践。有关如何在数据库中存储数据的最佳实践。这通常是通过减少存储数据的重复以及数据的访问方式来驱动的。
我发现，如果不首先考虑如何使用某个东西，就去思考它的结构几乎永远不会有成效。
我还会考虑稍后进行幼稚实施和重组的成本是多少。有时成本实际上并没有那么糟糕。还可以通过在第一次实现中考虑它来降低成本，您可以在其中设置额外的机制或进行部分数据突破以降低以后的成本。
The short answer is that there are no best practices to storing DNS entries. There are best practices for how to store data in a database. That is usually driven by reducing duplication of the stored data, and how that data will be accessed.
I find it's almost never productive to think about the structure of something without first going through how it's going to be used.
I would also consider what the cost is to doing a naive implementation and restructuring later. Sometimes the cost is actually not that bad. The cost can also be mitigated by considering it with the first implementation where you could set up additional mechanisms or do partial data break outs to reduce cost later.
请记住，一切都取决于您的预期用途。
您可能希望将 http:// 保留在单独的列或位字段中。您可能决定要区分 ftp、http、https 等。
顶级域名再次取决于预期用途。是否会有很多像 bbc.co.uk 这样的国际网站拥有多个顶级域名 (TLD) 列表？您可能需要单独使用它，因为将其分开可能是一个问题。再说一次，如果您认为您不会只使用 tld 来完成任何事情，那可能并不重要。不过，以正确的方式开始比中途改变路线要容易得多。
Bear in mind everything depends on your intended use.
You may want to keep the http:// but in a separate column or bit field. You may decide you want to differentiate between ftp, http, https, etc.
The tld again depends on intended use. Will there be a lot of international sites like bbc.co.uk with multiple tld listings? You will probably want that separately because it may be an issue to separate that out. Again, if you don't think you will be using just the tld for anything it may not matter. It will be a lot easier to start it the right way than to change course partway through, though.
根据您的具体查询需求，您可以将 URL 存储为单个字段，然后简单地使用正则表达式来解析它们并提取必要的组件。由于 URL 是专门为计算机解码而设计的，因此使用正则表达式解析它们是相当安全的。
当然，如果您正在搜索特定的内容，查询数据可能会更困难，但这需要为您的整体数据库设计提供信息。
Depending on your exact query needs, you can store URLs as a single field and simply use regular expressions to parse them and extract the necessary components. Since URLs are specifically formatted for computers to decipher, parsing them with regular expressions is fairly foolproof.
Of course querying the data can be harder if you are searching for something specific, but that will need to inform your overall database design.