Solved

best way to design table

Posted on 2013-11-05
10
242 Views
Last Modified: 2013-11-10
I have an excel spreadsheet that looks like this

                          January          February     March      etc....
John Smith             2                    2              2
Ann Smith             2                    0                2  
Joe Harvard           0                      2               2
etc...

I was thinking of making the months into columns and then making the rows according to the name and hours.  Any suggestions on how I should be designing this?
0
Comment
Question by:al4629740
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 167 total points
ID: 39625253
Hi Al,

While there are certain pros and cons to the differing styles, normalizing the date (month) into a single column is the easiest to use for most purposes.

CREATE TABLE xxx(
  user    varchar (30),
  period  date,
  hours    float
);

Open in new window


The alternative is something like this:

CREATE TABLE xxx(
  user    varchar (30),
  year    integer,
  January_hours float,
  February_hours float,
  March_hours  float,
  ... etc...
  December_hours float
);

Open in new window


A query to sum hours by year, quarter, etc. is trivial with the first.  It's a little more cumbersome with the second query.  Queries that want fiscal year hours (July to June) or any query across years can be particularly wordy with the second style.


Keep it simple!
Kent
0
 

Author Comment

by:al4629740
ID: 39625320
I would want to present the same format as above in a datagrid in vb6.  Would that pose a problem?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 39625597
You should normalize all data if possible.

So you should have "user_first" and "user_last" (and others, if needed), if possible, rather than just "user".  Frankly, I'd be more concerned with that normalization than the other.  I could live with denormalizing all months for the year in the same row if it would work much better for this specific table in this specific situation.

Another key thing is to cluster the table based on how it's most often going to be referenced:
( period, user_last, user_first )  OR
( user_last, user_first, period )  OR
etc..
Just don't cluster it on identity.
0
 

Author Comment

by:al4629740
ID: 39625712
Thank you
0
 

Author Comment

by:al4629740
ID: 39625714
What's the main difference between setting it as a non cluster and cluster in this case?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 39625791
Cluster is the table itself, so will automatically all columns in the table.

Nonclustered is a separate, duplicate copy of some/all columns in the table; any column not in the index requires a (costly) lookup back to the original table.

A normal data table should never have a non-clustered index only -- first put the proper clustered index on the table, then add non-clustered index(es) as needed.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 83 total points
ID: 39626302
Consider a report that compares this quarter with the corresponding quarter of last year (Quarter on Quarter).

These are far easier to furnish if the data is normalized as fieldnames don't change - just the date values. Also; Inserts and Updates are easier into normalized tables for similar reasons in my view.

>>I would want to present the same format as above in a datagrid in vb6.
It is possible to "pivot" the normalized data into the 12 column arrangement needed for your grid.

------
The alternative, to store in the 12 monthly columns, can be "unpivoted" into a normalized structure during the process of producing reports such as Quarter on Quarter.

Inserts and Updates are more complex in a de-normalized approach in my view. e.g. when updating a month's data you have to choose both the right row and the right column (fieldname) here.

------
i.e. It's a trade-off, either are possible. In general: normalize.
0
 

Author Comment

by:al4629740
ID: 39626414
Interesting.  So a cluster basically remakes the whole table then?
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 167 total points
ID: 39627194
Yes and no.

A clustered index ensures that rows with similar values (in the clustered key) are are stored "near" each other in the database.  The theory being that the designer wants a performance boost when data is accessed by the clustered key.

When the clustered key is created, the DBMS can do a lot of work to align the data according to the clustering rules.  During routine usage, things can get a bit tricky, and even a bit dicey.  When a new row is stored, or a row is modified so that the values in the clustered key change, the DBMS works to enforce the clustering.  The modified row can be particularly taxing on a busy DBMS as movement of the row to a page designated for the new clustered key value may occur when that happens, every index over the row must also be updated.

-  A clustered index can move a lot of data when it's created so that the data is aligned with the clustering.  
-  When a row is inserted, the overhead is similar to adding most any row.  
-  When a row is modified and the clustered key is not affected, the overhead is similar to modifying a row the does not have a clustered index.
-  When a row is modified and the value in the clustered key changes, the overhead may be significant the row may have to be relocated and all of the table indexes updated.


Kent
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 39627461
>> So a cluster basically remakes the whole table then? <<

Yes, 100%.

Again, a clustered index is the table itself.  So adding a clus index to a table that doesn't have one can re-write the entire table; if any nonclus index(es) exist on the table, all of them must be rebuilt as well.

The clustered key should (almost) never change because of the overhead involved in changing it.  It's easy to pick a value that won't change, such as user name, employee id, item number, identity value (last resort, never as a default "key"), etc..
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 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

19 Experts available now in Live!

Get 1:1 Help Now