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?
|
On Mar 9, 8:03 am, TheWoland <thewol...@gmail.com> wrote:
> 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.
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,
|
>> 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". <<
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.
|