1

I have following data stored in Excel which I would like to transfer to SQL DB. There will be a new value every year for every indicator.

Indicator | 2001 | 2002 | 2003  
-------------------------------
AACA      |    10|    20|    30
bbb       |   100|   200|   100
ccc       |    10|   300|   300

Does below project of SQL table follow SQL design rules?

+----+-----------+------+-------+
| Id | Indicator | Year | Value |
+----+-----------+------+-------+
|  1 | AACA      | 2001 |    10 |
|  2 | bbb       | 2001 |   100 |
|  3 | ccc       | 2001 |    10 |
|  4 | AACA      | 2002 |    20 |
|  5 | bbb       | 2002 |   200 |
|  6 | ccc       | 2002 |   300 |
|  7 | AACA      | 2003 |    30 |
|  8 | bbb       | 2003 |   100 |
|  9 | ccc       | 2003 |   300 |
  • For example data consistency - ID 9 2004 should be 2003 (just to avoid confusion) .. tried to add as an edit, but "edits must be at least 6 characters long" ... so.. couldn't. – Joishi Bodio Dec 30 '15 at 20:46
  • This design is OK. Some DBMS support this as the UNPIVOT operator. – Michael Green Jan 2 '16 at 3:24
  • @Joishi: thx, fixed – LAdas Jan 4 '16 at 10:18
2

You will want to add an alternate key/unique constraint upon the combination of Indicator and Year. By virtue of the value being stored at the intersection of Indicator and Year in the spreadsheet, you are only allowed a single value. The new table design would allow you to have as many entries for a given indicator and year unless you explicitly disallow that.

DDL is approximately

CREATE TABLE dbo.MyTable
(
    Id int IDENTITY(1,1)
,   Indicator varchar(10) NOT NULL
,   Year smallint NOT NULL
,   Value int NOT NULL
,   CONSTRAINT PK_MyTable PRIMARY KEY
(
    Id
)
,   CONSTRAINT AK_MyTable_Indicator_Year UNIQUE
(
    Indicator
,   Year
)
);
1

To me it does. This way you do not have to change the table nor your applications every year. If you would use the same 'layout' as your Excel sheet then the years would become repeating groups which does not comply to the first Normal Form (https://en.wikipedia.org/wiki/First_normal_form).

If you need to store specific information on your indicators the You could create a table for the indicators with that information. In that case you can replace the indicator column with the id of the indicator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.