我应该使用平面表还是规范化的数据库? [英] Should I use flat tables or a normalized database?

问题描述

我目前正在使用一个Web应用程序，该应用程序将MySQL数据库用作后端，在继续进行下去之前，我需要知道哪种方法对我的情况更有利.

I have a web application that I am currently working on that uses a MySQL database for the back-end, and I need to know what is better for my situation before I continue any further.

简单地说，在此应用程序中，用户将能够使用任何数字字段(由他们决定)构造自己的表单，现在我将其全部存储在由外键链接的几个表中.我的一个朋友建议为了使事情轻松/快速"，我应该将每个用户的表单转换为一个平面表，以便从他们那里查询数据保持快速(以防万一).

Simply put, in this application users will be able to construct their own forms with any number fields (they decide) and right now I have it all stored in a couple tables linked by foreign keys. A friend of mine suggests that to keep things "easy/fast" that I should convert each user's form to a flat table so that querying data from them stays fast (in case of large growth).

我应该使用外键(索引等)将所有数据集中到关系表中来使数据库保持规范化，还是应该为用户创建的每种新表单构造平面表?

Should I keep the database normalized with everything pooled into relational tables with foreign keys (indexes, etc) or should I construct flat tables for every new form that a user creates?

显然，创建平面表的一些好处是数据分离(安全)和查询速度将降低.但是，认真地我会从中获得多少收益呢?我真的不希望一直有10000个表并一直删除，更改和添加，但是如果它比我做的更好的话……我只需要一些输入即可.

Obviously some positives of creating flat tables is data separation (security) and query speeds would be cut down. But seriously how much gain would I get from this? I really don't want 10000 tables and to be dropping, altering, and adding all of the time, but if it will be better than I will do it... I just need some input.

谢谢

推荐答案

经验法则.从规范化到非规范化要比其他方法容易得多.

Rule of thumb. It's easier to go from normalized to denormalized than the other way around.

从合理级别的数据库规范化开始(合理地讲，我的意思是可读，可维护和高效，但未过早优化)，然后，如果随着性能的增长遇到性能问题，则可以选择研究非规范化的方式提高性能.

Start with a reasonable level of database normalization (by reasonable I mean readable, maintainable, and efficient but not prematurely optimized), then if you hit performance issues as you grow, you have the option of looking into ways in which denormalization may increase performance.

这篇关于我应该使用平面表还是规范化的数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！