The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database schema design question

Hi All,

First of all, please excuse me for the long post. The post is long but hopefully the problem described is fairly simple for somewhat experienced data modellers.

I am a beginner in database design so I was hoping that maybe you can give me some advice on how to design a schema for the following situation:

I have 8 sensors; each sensor has a bunch of parameters and I would like to store all this information in a SQL database.

The information is organized the following way:

Each sensor is identified by an "Index" and its data parameters by "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that all sensors have two pieces of data associated with them "Speed" and "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and "Threshold" is 10000. These two pieces of data, as mentioned before, are described by "SubIndexes", so in this case, SubIndex 1 would have a value of 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be described the following way:

Index 1001
SubIndex1 5000
SubIndex2 10000

As I mentioned before, I will have 8 sensors, so the whole information would be (w/ some example values) like this:

Index: 1001
SubIndex1: 5000
SubIndex2: 10000

Index: 1002
SubIndex1: 3000
SubIndex2: 17000


Index: 1008
SubIndex1: 2500
SubIndex2: 20000

The challening part is that I also have to store several "configurations" of the system where each configuration could have the sensors hold different data. For example:

Config 1:

Index: 1001
SubIndex1: 2000
SubIndex2: 3000


Config 2:
Index: 1001
SubIndex1: 1000
SubIndex2: 7000

Please note that the number of available configurations is not known at design time.

What is the best way to model this? What I came up with doesn't seem the most efficient. This is something that I thought it would work:

Create a table that has the following columns: Index, SubIndex, Data, ConfigNo.

For example:

Index SubIndex  Data  ConfigNo.
1001    1      2000      1
1001    2      3000      1
1001    1      1000      2
1001    2      7000      2

It seems as though I am repeating all the "indexes" and "subindexes" when these stay "constant" and only the data and configuration number changes. Is there any way to specify all the indexes and subindexes (all sensors) one time and to somehow make them "point" to various data given a certain configuration number? Or what would be a better method of doing it? I know all about primary/foreign key relationships but I still couldn't find a way to efficiently store this data.

Thank you for your time,
MVE Send private email
Saturday, January 06, 2007
I have changed the schema a little bit, and now I have it the following way:

I have two tables:

Table 1:

SensorID  Index
  1      1001
  2      1002
  8      1008

Table 2:

SensorID SubIndex  Value  ConfigNo.
  1        1      7000      1
  1        2      10000      1
  2        1      1000      1
  2        2      5000      1
  1        1      2000      2

This way, at least I tried to "normalize" the data so that if the "Index" of a sensor changes, I have to only change it in one place (in Table 1.) I am still not sure if I can make further improvements to this schema.

Thanks again,
MVE Send private email
Saturday, January 06, 2007
Your second schema looks good. You mentioned that the Index of a Sensor may change, so it makes sense to have a base Sensor table. Maybe you'll want to add additional information about Sensors in the future, and this way you're ready. You will also be able to use the primary/foreign key to insure you don't end up with an invalid Sensor Index in the data table.

You could go farther if you wanted - a Config table to store config information; a SubIndex table to store upper and lower limits on subindexes, etc. It all depends on how static your Index and SubIndex descriptions will be. If you knew the Index for the Sensor would never change, consider simply prefixing the Index integer with "100" whenever display was needed. That way you could get by with only one table.

Just to throw it out there (maybe you already know) - In most DBMS, using reserved keywords "Index" and "Value" for column names will cause hairloss when troubleshooting.
Mike Saeger Send private email
Saturday, January 06, 2007

"Just to throw it out there (maybe you already know) - In most DBMS, using reserved keywords "Index" and "Value" for column names will cause hairloss when troubleshooting"

Of course, you are right about this. I knew about the indexes but I didn't expect the name to be a problem. I of course quickly found out about it when my SQL queries wouldn't work. I had to resort to OLEDB to change the name "Index" to something else. :)

Thank you for your answer and analysis!
MVE Send private email
Sunday, January 07, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz