Database Design – How to Store Denormalized Excel Data

database-designtable

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 |

Best Answer

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
)
);
Related Question