Basic database design question

[hanvyj]

A warning: I'm very new to databases! Hopefully this will be a pretty simple question.

To simplfy my problem, I have a tree of 'meters' that collect data (Let's say electricity meters). I'd normally structure this with objects in a tree, each with a list or some kind of array of data. However, I'm trying to get some experience with databases, and this kind of system really should be based on a database.

So, my solution is this:

One Table containing the meters, any information around them (units etc) and their tree relationship/ID/readable name, contact for maintinence, you name it.

But how should I structure the other table that actually contains the data? I.e the half hourly, say, electricity readings?

With my normal OO style, I'd have this as a data structure with two columns: ID, A datetime stamp, and a value.

But with SQL I don't want a table for each meter, right? So do I have: ID, meterID, dateTime, Value and store all the data in the same table? I'd access individual meter's data by extracting from the meterdata table on MeterID (So, if I had two meters with 1000 entries, there'd be 2000 rows - and each 'MeterID' would be repeated 1000 times,

Like this:

1,Meter1, 01/02/2014, 01:00, 123
2,Meter1, 01/02/2014, 01:30, 125
3,Meter1, 01/02/2014, 02:00, 127
4,Meter2, 01/06/2013, 14:00, 18000
5,Meter2, 01/06/2013, 14:30, 17000
6,Meter2, 01/06/2013, 16:00, 18000

Thanks (total SQL newbie)

[Benjamin_11]

The best wsy to start your DB design is by answering two question:

1. How will the data be queried?
2. have a normalized the data as much as possible (ie. remove duplicates or simplify to the lowest level)

If you only need to query the indovudal meters then you almost have what you need excpt for the fact that each meter doesnt have a unique peice of data that identifies it. Consider using IDs for each meter, sich as rowds, to simply the queries and make them specific to each meter.

Give some more info about your needed query and I can go into more detail.

[hanvyj]

Thanks,

1) I haven't decided how I'm going to be interfacing with the data specificaly (I think I need to mess around a little more with SQL to try work out how I'm going to do it), but I'd expect the user to be able to do a few key things:

-Look at a meter and see the 'history' of say a month-year of data (plotted in a graph). The data will prbably have to be converted to common Units (I'm thinking it probably shouldn't be stored in those units).

-Look at a 'virtual' meter that's actually a calulation of other meters. So for example, if you wanted to look at the data for a building, it might be the the sum of three different floor meters (children in the tree). I imagine this would be pretty similar to above.

But the other fundemental part is going to be running some kind of reporting accross all of the meters in the database. This might be looking at hundreds of different meters (say, all the meters in a site) but only for one time-period.

I'm not sure how either of those will affect the design!

2) I don't believe there would be any duplicated data, as I understand it. I'm hoping to keep the base data as raw as possible, so whatever form it comes in from the meters - the tree strucuture will define how it should be converted.

I was certainly thinking along the lines of having a unique identifier for meters, and referencing the central data-table wherever possible rather than repeating data.

Thanks for the help