• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Which one will occupy less space in Database? More rows less columns or less rows more columns

Hi,

From the following two options, which one will occupy less space in database:

Option 1:
Table A has following columns:
Column 1 --> UserName
Column 2 --> Weekdayhits

For every user, this table will have 7 rows. Each row contains Daily hits of that user.

Option 2:
Table A has following columns:
Column 1 --> UserName
Column 2 --> SundayHits
Column 3 --> MondayHits
|
|
Column 8 --> SaturdayHits

Here, for each user, this table has only one row but 8 columns. Each column contains Daily hits of that user for that particular day.

We are assuming that there will be LOT OF USERS in both these cases.
Can you please tell us which option will occupy less space in database and why?

Appreciate your help!
0
meetpd
Asked:
meetpd
1 Solution
 
reb73Commented:
If data is populated for all days consistently for all users, they may be take more or less the same space..

Option1 may be preferable as data for some days could possibly be optional and need not be entered at all.. Just add a new column to indicate the weekday and index this column..

0
 
GawaiCommented:
well i m not quite sure about it.
u can test creating two diff tables to get the exact answer.
0
 
Bill BachPresidentCommented:
Much depends on the SQL engine and any indexing requirements, along with access requirements.  It can also depend on physical structure chosen by the SQL engine developer, and on the overhead incurred by indexing.

To answer your BASIC question, with all things being equal and data existing for every day, Option 2 will be smaller, simply because there will be 1/7th the overhead, or repetition of the key field.

However, the real answer lies even deeper.  First, you don't indicate a date field.  As such, there's probably more data being tracked that you don't indicate here. At minimum, I would expect to see three fields (Name, Date, and Hits) for option 1, although you can get away with a single date field for all 7 days in option 2, maintaining your 7:1 overhead savings ratio.  However, if you add 7 different date fields (one for each day), then your overhead savings ratio plummets.

So, having said that, the option 2 will definitely be smaller.  However, this is NOT the best solution.  In fact,  if some dates have 0 hits, then you can save space in Option 1 by skipping the record altogether.  The net savings will depend on how many dates have 0 hits, of course, so there's no way to estimate without knowing the resulting data set.  Furthermore, running reports from the data stored in Option 2 is a lot more painful.  It may be great for finding weekly values, but finding monthly values is QUITE difficult, as is finding results for any given combination of dates.  

An example that shows this would be calculating the number of hits for a month.  For option 1, it is a simple SQL query like:
    SELECT SUM(Hits) FROM Data WHERE MonthName(Date)='March'
Easy, right?  What would this look like with option 2?  I'm not even going to try to build a query, but you essentially need to find all weekly records that contained ANY days from March (so weekly records starting on February 22 or greater, or February 23 or greater on leap years).  For each record that lies WHOLELY in March, you can sum up the 7 different Hits values and add that to the total.  For records which lie PARTIALLY in March, you have to determine WHICH days are in March (handling leap days again) and then add ONLY those days to the total.  Can it be done in SQL?  Maybe, but *I'm* not going to do it.  ;-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now