Efficient Database Table Structure

Question

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??

score 4 · Answer 1 · answered Feb 17 '12 at 09:48

The first one will have better performance, disk storage and data retrieval will be better.

Having attribute names as varchars will make it impossible to change names, datatypes or apply any kind of validation
It will be impossible to index desired search actions
Saving integers as varchars will use more space
Ordering, adding or summing integers will be a headache, and will have bad performance
The programming language using this database will not have any possibility to have strong typed data

There are many more reasons for using the first approach.

score 4 · Accepted Answer · edited May 23 '17 at 12:03

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).

Efficient Database Table Structure

2 Answers2