A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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:
As I mentioned before, I will have 8 sensors, so the whole information would be (w/ some example values) like this:
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:
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.
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,
I have changed the schema a little bit, and now I have it the following way:
I have two tables:
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.
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.
"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!
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz