Time series data: Table design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time series data: Table design

Author

Topic

rahulchem
Starting Member

2 Posts

Posted - 2012-04-05 : 17:14:04

Hello,
I am creating a database for electricity usage for houses. There are many houses and each house has different gadgets that uses electricity. This is time series data. I have around 100 houses and 10 gadgets. I have 30 min data for an year. My current table looks as below. I am wondering, is there any better way to divide this single table in multple table to make it efficient?

time1, house1, gadget1, power usage
time1, house1, gadget2, power usage
time1, house2, gadget1, power usage
time1, house2, gadget2, power usage
time2, house1, gadget1, power usage
:
:
timen, house2, gadget1, power usage
timen, house2, gadget2, power usage

Thanks in advance,
Rahul

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-05 : 17:32:24

This should be split into 3 tables:

create table houses(houseID int not null primary key,
Address varchar(100) not null unique)

create table gadgets(gadgetID int not null primary key,
gadgetName varchar(50) not null,
houseID int null references houses(houseID))

create table electicUse(gadgetID int not null references gadgets(gadgetID),
readtime datetime not null default(getdate()),
reading decimal(20,5) not null,
constraint pk_electricuse primary key(readtime,gadgetid))

Isolate the houses and gadgets from the readings. I'm assuming each gadget is unique and only belongs to one house. Now your readings only need the gadget ID, the time, and the reading. You never have to worry about the wrong gadget being listed with the wrong house, or with multiple readings at the same time.

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-04-06 : 15:32:51

I assume you'll be comparing gadgets of the same type against each other in different homes. if so robvolk's design will not accommodate this easily. If this is something you will want to do then: To facilitate this take robvolk's design, but remove the house ID from the gadget table and create a house-gadget cross reference table which contains a new id for the house gadget type combo, along with the gadgetid and houseid. Then have this new house-gadgetid replace that in robvolk's electicUse table.

Look up 3NF or "third normal form" for assistance in data base design.

Likes to run, hates the runs!

rahulchem
Starting Member

2 Posts

Posted - 2012-04-07 : 00:32:25

Thanks Pduffin and Robvolk.

I did want to compare gadgets of same type against each other in different home. I guess, both of above answers have solved my problem.

Thanks you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources