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!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!
"vvf" <novvfspam@.hotmail.com> wrote in message
news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> 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!
>|||I'd think you'd want something more like:
Table Configs
Config SensorId Parm1 Parm2
1 1001 123 456
1 1002 234 567
...
2 1001 987 654
2 1002 876 543
...
Table Samples
SampleId SensorId Value
1 1001 1.3
1 1002 -7.2
...
Table SampleIdConfig
SampleId Config
1 7
2 3939
...
Or you could at small cost eliminate the last table and just add a
config column to the Samples table, it requires denormalizing the
column, but it's not like it ever changes!
J.
On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>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!
>"vvf" <novvfspam@.hotmail.com> wrote in message
>news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> 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!
>>
>|||On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>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
Index is a bad choice of name, it is a reserved word in SQL. From the
given data I would be inclined to call it Sensor. Likewise SubIndex
sounds like it would better be named Measure, and for Data I might be
inclined toward Value. These are small things, but they are worth
getting right as early in the process as possible.
That structure looks fine to me, as far as I can say from the given
data. It has a three part key (Index, SubIndex, ConfigNo), and one
column of data.
Does SubIndex have the same meaning across all values of Index? If
SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
other Index that has a SubIndex of 1? In general it would be a VERY
good idea to maintain that sort of consistency if measures are common
across multiple indexes.
Does ConfigNo have any meaning across multiple values of Index? Is
someone going to say "now for ConfigNo = 7, show me each Index and the
associated Data"?
Any time I have seen sensor data in a database there has been a time
dimension somewhere. As I read your description I kept wondering when
it would show up.
Roy Harvey
Beacon Falls, CT|||Thanks JXStern.
I should have mentioned that the sensors could have as many as 255
subindexes (or params as you call them.)
The problem is that a table would have to have quite a few columns to go
with the schema that you proposed. I wasn't sure if this would pose a
problem in terms of efficiency or if it is even possible to have more than
255 columns in SQL Mobile (I should check books online for that.)
Thanks again,
vvf.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
> I'd think you'd want something more like:
> Table Configs
> Config SensorId Parm1 Parm2
> 1 1001 123 456
> 1 1002 234 567
> ...
> 2 1001 987 654
> 2 1002 876 543
> ...
> Table Samples
> SampleId SensorId Value
> 1 1001 1.3
> 1 1002 -7.2
> ...
> Table SampleIdConfig
> SampleId Config
> 1 7
> 2 3939
> ...
>
> Or you could at small cost eliminate the last table and just add a
> config column to the Samples table, it requires denormalizing the
> column, but it's not like it ever changes!
> J.
>
> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >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!
> >
> >"vvf" <novvfspam@.hotmail.com> wrote in message
> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> >> 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!
> >>
> >>
> >
>|||Hi Roy,
Thank you for your detailed answer. Please see comments embedded below:
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >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
> Index is a bad choice of name, it is a reserved word in SQL.
Correct. I had to resort to OLEDB to rename the column as I would get an
error trying to run SQL commands against this type of schema.
> From the
> given data I would be inclined to call it Sensor. Likewise SubIndex
> sounds like it would better be named Measure, and for Data I might be
> inclined toward Value. These are small things, but they are worth
> getting right as early in the process as possible.
Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
instead of "Index" now. I "imported" the terminology (i.e., "Index",
"SubIndex") from the communications protocol "CANOPEN" as this is what we
use in our systems and had to have some sort of "consistency" throughout our
design.
> That structure looks fine to me, as far as I can say from the given
> data. It has a three part key (Index, SubIndex, ConfigNo), and one
> column of data.
> Does SubIndex have the same meaning across all values of Index? If
> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
> other Index that has a SubIndex of 1?
Yes. All the SubIndexes have the exact same meaning across sensors. For
example, "SubIndex1" is "Speed" for all 8 sensors.
> In general it would be a VERY
> good idea to maintain that sort of consistency if measures are common
> across multiple indexes.
> Does ConfigNo have any meaning across multiple values of Index? Is
> someone going to say "now for ConfigNo = 7, show me each Index and the
> associated Data"?
Yes, they could say that. Basically, it is saving various configurations of
the sensors. For example, I could set up my sensors so that Sensor 1 has
SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
I would save this "scenario" as ConfigNo "1". After that, I could change the
settings for these two sensors to something else and then save that
"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
would be filled with the corresponding values saved previously. Of course,
in this example I only showed 2 sensors while in the real scenario I would
have 8 sensors set to certain data.
> Any time I have seen sensor data in a database there has been a time
> dimension somewhere. As I read your description I kept wondering when
> it would show up.
The sensor data shown here is more like "configuration" data; In other
words, it is "static" data in a way because it only defines how the sensors
should behave when acquiring their data. That particular data (acquired
data) is saved in a different table and indeed has a time associated with
it. The data that we talked about here only configures the sensors so they
act in a certain way when acquiring data, and, as I mentioned earlier, I
have to have various pre-defined configurations for them.
Thanks again for your answer and analysis!
vvf.|||Your responses confirm to me that the design still looks good. The
only other comment I have is that you should be sure to have "master"
tables for Index, SubIndex and Configuration, even if the data is no
more than a key and description. But I expect you already have done
that.
Roy Harvey
Beacon Falls, CT
On Sun, 7 Jan 2007 18:24:08 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Hi Roy,
>Thank you for your detailed answer. Please see comments embedded below:
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
>> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >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
>> Index is a bad choice of name, it is a reserved word in SQL.
>Correct. I had to resort to OLEDB to rename the column as I would get an
>error trying to run SQL commands against this type of schema.
>> From the
>> given data I would be inclined to call it Sensor. Likewise SubIndex
>> sounds like it would better be named Measure, and for Data I might be
>> inclined toward Value. These are small things, but they are worth
>> getting right as early in the process as possible.
>Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
>instead of "Index" now. I "imported" the terminology (i.e., "Index",
>"SubIndex") from the communications protocol "CANOPEN" as this is what we
>use in our systems and had to have some sort of "consistency" throughout our
>design.
>> That structure looks fine to me, as far as I can say from the given
>> data. It has a three part key (Index, SubIndex, ConfigNo), and one
>> column of data.
>> Does SubIndex have the same meaning across all values of Index? If
>> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
>> other Index that has a SubIndex of 1?
>Yes. All the SubIndexes have the exact same meaning across sensors. For
>example, "SubIndex1" is "Speed" for all 8 sensors.
>> In general it would be a VERY
>> good idea to maintain that sort of consistency if measures are common
>> across multiple indexes.
>
>> Does ConfigNo have any meaning across multiple values of Index? Is
>> someone going to say "now for ConfigNo = 7, show me each Index and the
>> associated Data"?
>Yes, they could say that. Basically, it is saving various configurations of
>the sensors. For example, I could set up my sensors so that Sensor 1 has
>SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
>to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
>I would save this "scenario" as ConfigNo "1". After that, I could change the
>settings for these two sensors to something else and then save that
>"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
>would be filled with the corresponding values saved previously. Of course,
>in this example I only showed 2 sensors while in the real scenario I would
>have 8 sensors set to certain data.
>> Any time I have seen sensor data in a database there has been a time
>> dimension somewhere. As I read your description I kept wondering when
>> it would show up.
>The sensor data shown here is more like "configuration" data; In other
>words, it is "static" data in a way because it only defines how the sensors
>should behave when acquiring their data. That particular data (acquired
>data) is saved in a different table and indeed has a time associated with
>it. The data that we talked about here only configures the sensors so they
>act in a certain way when acquiring data, and, as I mentioned earlier, I
>have to have various pre-defined configurations for them.
>Thanks again for your answer and analysis!
>vvf.
>|||On Sun, 7 Jan 2007 18:06:51 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Thanks JXStern.
>I should have mentioned that the sensors could have as many as 255
>subindexes (or params as you call them.)
Hmm. Well, for now I'd say just go for it, 255 columns. You could
further normalize by breaking them into a table keyed by config,
sensorid, and parm#, but that's probably overkill. Depending on what
you do with the data downstream, it's probably more efficient just to
have the 255 columns. Such natural sets of columns are formally a
"repeating group" which is forbidden in even 1NF, ... BUT frequently
kept together anyway, AS LONG AS THEY CONSTITUTE A SET, that is,
PARM#1 is not really the same thing as PARM#2.
>The problem is that a table would have to have quite a few columns to go
>with the schema that you proposed. I wasn't sure if this would pose a
>problem in terms of efficiency or if it is even possible to have more than
>255 columns in SQL Mobile (I should check books online for that.)
>Thanks again,
>vvf.
>
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
>> I'd think you'd want something more like:
>> Table Configs
>> Config SensorId Parm1 Parm2
>> 1 1001 123 456
>> 1 1002 234 567
>> ...
>> 2 1001 987 654
>> 2 1002 876 543
>> ...
>> Table Samples
>> SampleId SensorId Value
>> 1 1001 1.3
>> 1 1002 -7.2
>> ...
>> Table SampleIdConfig
>> SampleId Config
>> 1 7
>> 2 3939
>> ...
>>
>> Or you could at small cost eliminate the last table and just add a
>> config column to the Samples table, it requires denormalizing the
>> column, but it's not like it ever changes!
>> J.
>>
>> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >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!
>> >
>> >"vvf" <novvfspam@.hotmail.com> wrote in message
>> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> >> 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!
>> >>
>> >>
>> >
>
