Solved

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

Posted on 2009-04-01
3
448 Views
Last Modified: 2013-12-25
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
Comment
Question by:meetpd
3 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24041718
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
 
LVL 20

Expert Comment

by:Gawai
ID: 24041740
well i m not quite sure about it.
u can test creating two diff tables to get the exact answer.
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 250 total points
ID: 24043353
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now