高效的数据库表结构
考虑 Microsoft SQL Server 2008
我需要创建一个表，可以通过以下两种不同的方式创建该表。
Structure Columnwise
StudentId number, Name Varchar, Age number, Subject varchar
eg.(1,'Dharmesh',23,'Science')
(2,'David',21,'Maths')
Structure Rowwise
AttributeName varchar,AttributeValue varchar
eg.('StudentId','1'),('Name','Dharmesh'),('Age','23'),('Subject','Science')
('StudentId','2'),('Name','David'),('Age','21'),('Subject','Maths')
在第一种情况下，记录会更少，但在第二种方法中，记录会增加 4 倍，但会减少 2 列。
那么哪种方法在性能、磁盘存储和数据重试方面更胜一筹？
Consider Microsoft SQL Server 2008
I need to create a table which can be created two different ways as follows.
Structure Columnwise
StudentId number, Name Varchar, Age number, Subject varchar
eg.(1,'Dharmesh',23,'Science')
(2,'David',21,'Maths')
Structure Rowwise
AttributeName varchar,AttributeValue varchar
eg.('StudentId','1'),('Name','Dharmesh'),('Age','23'),('Subject','Science')
('StudentId','2'),('Name','David'),('Age','21'),('Subject','Maths')
in first case records will be less but in 2nd approach it will be 4 times more but 2 columns are reduced.
So which approach is more better in terms of performance,disk storage and data retrial??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
第一个会有更好的性能，磁盘存储和数据检索会更好。
使用第一种方法还有很多原因。
The first one will have better performance, disk storage and data retrieval will be better.
There are many more reasons for using the first approach.
您的第二种方法通常称为 EAV 设计-实体-属性-值。
恕我直言，一直是第一种方法。这使您可以正确键入列，从而实现最有效的数据存储，并极大地有助于轻松高效地进行查询。
根据我的经验，EAV 方法通常会带来巨大的痛苦。 这是一个有关此问题的上一个问题的示例，其中包含指向最佳实践的良好链接。如果您进行搜索，您会发现更多 - 非常值得仔细检查。
人们选择 EAV 路线的一个常见原因是对灵活的模式进行建模，这在 RDBMS 中相对难以高效地完成。其他方法包括将数据存储在 XML 字段中。这是 NOSQL（非关系）数据库由于其无模式性质（例如 MongoDB）而非常方便的原因之一。
Your second approach is commonly known as an EAV design - Entity-Attribute-Value.
IMHO, 1st approach all the way. That allows you to type your columns properly allowing for most efficient storage of data and greatly helps with ease and efficiency of queries.
In my experience, the EAV approach usually causes a world of pain. Here's one example of a previous question about this, with good links to best practices. If you do a search, you'll find more - well worth a sift through.
A common reason why people head down the EAV route is to model a flexible schema, which is relatively difficult to do efficiently in RDBMS. Other approaches include storing data in XML fields. This is one reason where NOSQL (non-relational) databases can come in very handy due to their schemaless nature (e.g. MongoDB).