I have a number of "clients" which report with a string
"client_id,client_model,0001001001", when the third part is a binary
string containing client's status. Each client model can have various
status message length and interpretation.
For each of the locations in the status message, stands it's
description and it's "importance level".

Example:
For model A:
Bit location: 0 1 2 3
Description: "General alarm" "Error no1" "Error no2" "Unit accessed"
Importance: "Critical" "Major" "Minor" "None"

For device B:
Bit location: 0 1 2
Description: "General alarm" "Error no1" "Unit accessed"
Importance: "Critical" "Major" "None"

What will be the best way to store this data into the database?
Any help will be appreciated.

On Mar 9, 8:03 am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
I am a little unclear based on your sample data and the example
descriptions. Is the binary string a fixed length ? So model A uses
only the first 4 character bits and all the others should be zero??

For database design, start by asking what is it that you are modeling?
each thing is an entity in the model. Looks like you have a few
entities.

Clients
Device Model
Status

What attributes does a client have? Let's assume a name and an address
Entity Client
attributes name, address, other client info.

How about models? Similar info.

Entity DeviceModel
attributes model#, name, features, other model info.

then come your status codes. These are specific to each device, so
I'll use the model# to identify the device.
Entity DeviceStatus
attributes model#, bit location, status description, importance

Lastly (in the model, but it was first in your post), is the status
"messages" for specific client devices
Entity ClientDeviceStatus
attributes client_name, model#, status bit code
where the status code needs to be mapped to the bit location value in
the device status entity. It may be desirable to list the bit
locations individually, if the list is short. So this entity might
then have these attributes:
client_name, model#, status bit 0, status bit 1, status bit 2, status
bit 3
Or this might be normalized to something like this
Entity ClientDeviceStatusList
attributes client_name, model#, status bit location
Assuming only those locations that are ON (1) are included in this
entity. And if only ONE of the bits may be ON, then the candidate
Primary Key might be Client name and model#. Else the bit location
might have to be part of the PK as well.

Bottom line is to start asking what entities your are going to include
in your data model. Second phase would be noting how the entities
relate, which may change what attributes they include. later still
you will prepare the model by normalizing it. only then should you
start mapping entities to DB tables.

HTH,
Ed

On Mar 9, 8:03 am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
I am a little unclear based on your sample data and the example
descriptions. Is the binary string a fixed length ? So model A uses
only the first 4 character bits and all the others should be zero??

For database design, start by asking what is it that you are modeling?
each thing is an entity in the model. Looks like you have a few
entities.

Clients
Device Model
Status

What attributes does a client have? Let's assume a name and an address
Entity Client
attributes name, address, other client info.

How about models? Similar info.

Entity DeviceModel
attributes model#, name, features, other model info.

then come your status codes. These are specific to each device, so
I'll use the model# to identify the device.
Entity DeviceStatus
attributes model#, bit location, status description, importance

Lastly (in the model, but it was first in your post), is the status
"messages" for specific client devices
Entity ClientDeviceStatus
attributes client_name, model#, status bit code
where the status code needs to be mapped to the bit location value in
the device status entity. It may be desirable to list the bit
locations individually, if the list is short. So this entity might
then have these attributes:
client_name, model#, status bit 0, status bit 1, status bit 2, status
bit 3
Or this might be normalized to something like this
Entity ClientDeviceStatusList
attributes client_name, model#, status bit location
Assuming only those locations that are ON (1) are included in this
entity. And if only ONE of the bits may be ON, then the candidate
Primary Key might be Client name and model#. Else the bit location
might have to be part of the PK as well.

Bottom line is to start asking what entities your are going to include
in your data model. Second phase would be noting how the entities
relate, which may change what attributes they include. later still
you will prepare the model by normalizing it. only then should you
start mapping entities to DB tables.

HTH,
Ed

On Mar 9, 8:03 am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
I am a little unclear based on your sample data and the example
descriptions. Is the binary string a fixed length ? So model A uses
only the first 4 character bits and all the others should be zero??

For database design, start by asking what is it that you are modeling?
each thing is an entity in the model. Looks like you have a few
entities.

Clients
Device Model
Status

What attributes does a client have? Let's assume a name and an address
Entity Client
attributes name, address, other client info.

How about models? Similar info.

Entity DeviceModel
attributes model#, name, features, other model info.

then come your status codes. These are specific to each device, so
I'll use the model# to identify the device.
Entity DeviceStatus
attributes model#, bit location, status description, importance

Lastly (in the model, but it was first in your post), is the status
"messages" for specific client devices
Entity ClientDeviceStatus
attributes client_name, model#, status bit code
where the status code needs to be mapped to the bit location value in
the device status entity. It may be desirable to list the bit
locations individually, if the list is short. So this entity might
then have these attributes:
client_name, model#, status bit 0, status bit 1, status bit 2, status
bit 3
Or this might be normalized to something like this
Entity ClientDeviceStatusList
attributes client_name, model#, status bit location
Assuming only those locations that are ON (1) are included in this
entity. And if only ONE of the bits may be ON, then the candidate
Primary Key might be Client name and model#. Else the bit location
might have to be part of the PK as well.

Bottom line is to start asking what entities your are going to include
in your data model. Second phase would be noting how the entities
relate, which may change what attributes they include. later still
you will prepare the model by normalizing it. only then should you
start mapping entities to DB tables.

HTH,
Ed

various status message length and interpretation. For each of the
locations in the status message, stands it's description and it's
"importance level". <<

This is a **very** denormalized design. That is about all we can say
without more specs. You have what I call an "Automobiles, Squids and
Britney Spears" design -- many unrelated things forced into a single
structure that mixes data and metadata together, inviolation of any
kindof Normal Form. You should handle parsing this mess in the front
end before it gets to the database. Oh, are these binary strings high-
end or low-end, etc.? Then insert the data into normalized tables.

various status message length and interpretation. For each of the
locations in the status message, stands it's description and it's
"importance level". <<

This is a **very** denormalized design. That is about all we can say
without more specs. You have what I call an "Automobiles, Squids and
Britney Spears" design -- many unrelated things forced into a single
structure that mixes data and metadata together, inviolation of any
kindof Normal Form. You should handle parsing this mess in the front
end before it gets to the database. Oh, are these binary strings high-
end or low-end, etc.? Then insert the data into normalized tables.

various status message length and interpretation. For each of the
locations in the status message, stands it's description and it's
"importance level". <<

This is a **very** denormalized design. That is about all we can say
without more specs. You have what I call an "Automobiles, Squids and
Britney Spears" design -- many unrelated things forced into a single
structure that mixes data and metadata together, inviolation of any
kindof Normal Form. You should handle parsing this mess in the front
end before it gets to the database. Oh, are these binary strings high-
end or low-end, etc.? Then insert the data into normalized tables.

On Mar 9, 4:03*am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
Let me paraphrase your question...

You're driving up to Manhattan from New Jersey, your transmission is
failing, your fuel gauge is showing empty, you've got a dead body in
your trunk. So you stop at the gas station and you ask the user group
- should I put in $10 of diesel or $8 kerosene in my car (that happens
to use regular unleaded)? Someone mentioned above that your structure
seemed denormalized... he was politely suggesting that the design is
completely wrong.

My suggestion is for you to read a good beginner book on SQL - Head
First SQL is not too bad. If you spent 3 days reading the book (or
another on database/database theory) you will reap huge benefits from
the investment.

On Mar 9, 4:03*am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
Let me paraphrase your question...

You're driving up to Manhattan from New Jersey, your transmission is
failing, your fuel gauge is showing empty, you've got a dead body in
your trunk. So you stop at the gas station and you ask the user group
- should I put in $10 of diesel or $8 kerosene in my car (that happens
to use regular unleaded)? Someone mentioned above that your structure
seemed denormalized... he was politely suggesting that the design is
completely wrong.

My suggestion is for you to read a good beginner book on SQL - Head
First SQL is not too bad. If you spent 3 days reading the book (or
another on database/database theory) you will reap huge benefits from
the investment.

On Mar 9, 4:03*am, TheWoland <thewol... (AT) gmail (DOT) com> wrote:
Let me paraphrase your question...

You're driving up to Manhattan from New Jersey, your transmission is
failing, your fuel gauge is showing empty, you've got a dead body in
your trunk. So you stop at the gas station and you ask the user group
- should I put in $10 of diesel or $8 kerosene in my car (that happens
to use regular unleaded)? Someone mentioned above that your structure
seemed denormalized... he was politely suggesting that the design is
completely wrong.

My suggestion is for you to read a good beginner book on SQL - Head
First SQL is not too bad. If you spent 3 days reading the book (or
another on database/database theory) you will reap huge benefits from
the investment.