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

best way to design table

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
al4629740
Asked:
al4629740
  • 4
  • 3
  • 2
  • +1
6 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
al4629740Author Commented:
I would want to present the same format as above in a datagrid in vb6.  Would that pose a problem?
0
 
Scott PletcherSenior DBACommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
al4629740Author Commented:
Thank you
0
 
al4629740Author Commented:
What's the main difference between setting it as a non cluster and cluster in this case?
0
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulCommented:
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
 
al4629740Author Commented:
Interesting.  So a cluster basically remakes the whole table then?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Scott PletcherSenior DBACommented:
>> 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now