How to store this string

Hello,
I have string that I need to store in a database, but I don't know exactly
how. These strings could look like this:
A001111105110416352489
I parse the string through a RegEx looking like this:
^([A])(\d{3})([0|1]{3})([0|1])(\w*)
A = Mode, must be A in this case
001 = Component address
111 = 3-button status, can be 000, 001, 010, 100, 101, 110, 011 etc...
0 = Can be 0 or 1
05110416352489 = If above is 1, then this is a barcode, otherwise this isn't
sent.
Currently I store the above values in a field each. But the problem is that
I would like to store each button status separately and there might be
4-buttons, 5-buttons and so on in the future. I don't believe it's a good
idea to store each digit as a new field, but is there another way?
Here's the table:
CREATE TABLE [Actions](
[action_id] [int] IDENTITY(1,1) NOT NULL,
[component_address] [nvarchar](10) NOT NULL,
[time_stamp] [datetime] NOT NULL,
[barcode] [nvarchar](50) NULL,
[action_mode] [nvarchar](1) NOT NULL,
[button_status] [nvarchar](10) NULL,
CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
(
[action_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Thanks!
Cheers,
JonahI don't really understand what the button status thing represents in the
"real world", so it's hard to say whether storing them seperately is a
good idea or a bad idea. But if you do store the combined status of the
button(s) in a single column, you could have user defined functions to
pull out individual states (and add them to the table as computed
columns, or just use them when you need them).|||Sorry, I noticed that as well when reading my question again...
The 3-button status represents a component in the real-world system with
three buttons on it. When a button is pushed (say the 3rd one), it reports
001. When released 000. If I push the first, keeps it down and press the
third button, it reports 101, and when releasing them both 100 (or 001
depending on what was released first) and finally 000.
Since we track the time for each action on the component, I would sometimes
like to know for how long certain buttons has been pushed. I would also like
to pull statistics for a separate button (say the 2nd one) - how often is it
pushed, what time of the day etc.
I hope this gives some information about the real-world representation.
Cheers,
Jonah|||Are Defined Functions the deal here?
/Jonah
"Jonah Olsson" <jonah@.IHATESPAM.com> skrev i meddelandet
news:uiW%23NEvhGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Sorry, I noticed that as well when reading my question again...
> The 3-button status represents a component in the real-world system with
> three buttons on it. When a button is pushed (say the 3rd one), it reports
> 001. When released 000. If I push the first, keeps it down and press the
> third button, it reports 101, and when releasing them both 100 (or 001
> depending on what was released first) and finally 000.
> Since we track the time for each action on the component, I would
> sometimes like to know for how long certain buttons has been pushed. I
> would also like to pull statistics for a separate button (say the 2nd
> one) - how often is it pushed, what time of the day etc.
> I hope this gives some information about the real-world representation.
> Cheers,
> Jonah
>|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Currently I store the above values in a field each. But the problem is
> that I would like to store each button status separately and there might
> be 4-buttons, 5-buttons and so on in the future. I don't believe it's a
> good idea to store each digit as a new field, but is there another way?
> Here's the table:
> CREATE TABLE [Actions](
> [action_id] [int] IDENTITY(1,1) NOT NULL,
> [component_address] [nvarchar](10) NOT NULL,
> [time_stamp] [datetime] NOT NULL,
> [barcode] [nvarchar](50) NULL,
> [action_mode] [nvarchar](1) NOT NULL,
> [button_status] [nvarchar](10) NULL,
> CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
> (
> [action_id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
The normal way would be to replace button_status with
CREATE TABLE ButtonStatus (
action_id int NOT NULL,
button_no tinyint NOT NULL,
button_status bit NOT NULL,
CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
REFERENCES Atcions(actio_id)
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> The normal way would be to replace button_status with
> CREATE TABLE ButtonStatus (
> action_id int NOT NULL,
> button_no tinyint NOT NULL,
> button_status bit NOT NULL,
> CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
> CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
> REFERENCES Atcions(actio_id)
> )
Ah, good one! So button_no is the button "position" from left to right? 0 1
2?
/Jonah|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Ah, good one! So button_no is the button "position" from left to right? 0
> 1 2?
Yes. As for 0-2 vs. 1-3 or right/left vs. left/right, that is up to you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jonah Olsson wrote:

> Currently I store the above values in a field each. But the problem is tha
t
> I would like to store each button status separately and there might be
> 4-buttons, 5-buttons and so on in the future. I don't believe it's a good
> idea to store each digit as a new field, but is there another way?
If you can be reasonably confident that there will never be more than
31 buttons, use an integer, and create defined functions that pull out
individual button statuses from an integer.
This would be a more compact representation than the fully normalized
representation that Erland suggested; his is better in some ways though
-- it would be straightforward to search by button number, for example
("find me a time when button 1 was pressed within 3 seconds of button
2, but button 4 hadn't been pressed in the previous 60 seconds and
button 3 wasn't pressed in the following 120 seconds"). That sort of
query would be pretty impossible *without* full normalization.|||Great! Thank you both.
Cheers,
Jonah