simple problem with progression thru states 1

[skiflyer]

I know this is a simple problem, I know I've done it a million times before, but I am just drawing blanks left and right this afternoon...

I have an entity, it progresses thru several states. I want to keep a history of these states, so I create a new entry in my status table for each state.

I'm coming across one problem because sometimes my states occur in such quick succession that the timestamps on the two rows are equal.

I'm also having a little trouble writing the queries to determine all of the entities in a current state.

The solution that comes to mind immediately is to add a "state closed" boolean field, but this seems sloppy to me. Is this a good solution, or perhaps I'm overlooking something obvious?

i.e.

Table1
entity_id (fk)
status
timestamp

so now just to find out what status an entity is in I have to write
SELECT max(timestamp) FROM Table1 WHERE entity_id = X
then take that return, and run a 
SELECT status FROM Table1 WHERE entity_id = X AND timestamp=(returned value)

but this is sometimes giving me two rows, so I need to fudge the results.

I dunno, the more I ramble the more a boolean stating whether or not the state is alive sounds good.... (especially when thinking of a "all entity_id's in state=Y", but I'd appreciate any desenting opinions.

Thanks,
Rob

 

[sleipnir214]

I have a couple of ideas to throw at you.

How about using a multicolumn primary key where the first column is your timestamp and the second column is an auto_increment? (

http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html)

If the first column is a duplicate of a previous value, MySQL will provide an incremented value for the auto_increment column. Otherwise, MySQL will generate a 1 for the auto_increment column.

Your primary key will always be unique. It might help you find the one value you need.



Another:
Do you need to know the state history of each entity? If not, just have an entity create an entry when it enters the first state. Then have the entity update that record as it changes states.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

I do need to know the state history... so the second option is out. The first option is intriguing, I like the idea. Although this is in reality a weak entity table with no primary key.

So, if I were using SQL Server or some such, it'd be a no brainer to take your first suggestion. But without subqueries, is there a way I can then find the current state of a given entity with a single query?

Something like (logical here not SQL)

SELECT * FROM Table1 
WHERE autoIncremented = MAX(autoIncremented) 
AND entityId = X

Actually, the only other issue, I just realized when I entered the above query, is that I'm not guaranteed an auto-increment will necessarily be higher... if records are inadvertently deleted and the table is cleaned, isn't the autoincrementer set to fill in the back #'s?

With the exception of the fact that I need to remember to close an old state everytime I need to open a new state, do you see any flaws with that approach?

-Rob

 

[sleipnir214]

Here's the test table I'm using:

CREATE TABLE foo
(
  record_time timestamp(14) NOT NULL,
  record_id int(10) unsigned NOT NULL auto_increment,
  entity_status enum('one','two','three') NOT NULL default 'one',
  entity_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (record_time,record_id)
)

Which contains the following data:

+----------------+-----------+---------------+-----------+
| record_time    | record_id | entity_status | entity_id |
+----------------+-----------+---------------+-----------+
| 20030819175049 |         1 | one           |         1 |
| 20030819175049 |         2 | two           |         1 |
| 20030819175049 |         3 | one           |         2 |
| 20030819175049 |         4 | two           |         2 |
| 20030819175049 |         5 | three         |         1 |
| 20030820092445 |         1 | one           |         3 |
+----------------+-----------+---------------+-----------+

<aside>
Inserts are accomplished by queries like the following:

insert into foo (status, entity_id) values ('three', 1);

</aside>


If I perform the following query:

select record_time, record_id, entity_status, entity_id from foo
where entity_id = 2
order by record_time desc, record_id desc
limit 1

I get the following return:

+----------------+-----------+---------------+-----------+
| record_time    | record_id | entity_status | entity_id |
+----------------+-----------+---------------+-----------+
| 20030819175049 |         4 | two           |         2 |
+----------------+-----------+---------------+-----------+

Is this the behavior you're looking for?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

Yes, it's exactly what I asked. But, sorry, I didn't ask the more important question.

The most common query I'll be running is (by far, this query will be run every second, whereas the others will be run on demand)... collect all the packages currently in state one.

now, with a state closed field I simply run

SELECT entity_id 
FROM Table1 
WHERE state='one' AND state_closed=0

Returning the very few records in an open state of one

Without one, I'm not a 100% but I believe I'll have a bad situation of needing to return all of the current states for each entity (which will grow quickly) and seeing if that current state is one.

Or am I just thinking about the alternative incorrectly?

-Rob
(sorry for the delay, we're not getting incoming email today and no one told me)

 

[sleipnir214]

No, you're right. The schema I've suggested makes it difficult to answer that question.

But here's another look at the question:

The current state of an entity is an attribute of the entity. The time an entity entered a particular state can be thought of as an attribute of the state itself.

With this thinking, we go to a pair of related tables:

create table entity
(
	entity_id int unsigned,
	entity_state enum ('state_one', 'state_two', 'state_three', 'state_finished') default 'state_one'
)

create table entity_state
(
	entity_id int unsigned,
	entity_state enum ('state_one', 'state_two', 'state_three', 'state_finished'),
	entity_state_entry timestamp
)

Then when an entity enters the system, you insert two records:

insert into entity (entity_id) values (1);
insert into entity_state (entity_id, entity_state) values (1, 'state_one');

As the entity progresses through the states, you update the entity's record and insert a new state record:

update entity set entity_state = 'state_two' where entity_id = 1;
insert into entity_state (entity_id, entity_state) values (1, 'state_two');

To find out the current state of an entity:

SELECT entity_state from entity where entity_id = 1;

To find out all the entities that are in a given state:

SELECT entity_id from entity where entity_state = 'state_two';

Finding out how long a particular entity was in a given state will be a little more complicated, but easy to do in frontend programming code.


I'm not sure if this is really superior, in terms of database schemas, to your idea of closing a state record. But it might make your database code a little easier. In order to be able to close a state, your database code will have to constantly keep track of the current state record as it changes. My way, the database code is updating the same entity record every time and inserting a new state record in the related table.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

Indeed, both sides have their drawbacks. I have to say, this is the first time I'm missing subqueries since I started using MySQL.

Thanks for the all the back and forth with me, gives me lots to think about before finalizing my database.

-Rob

 

[sleipnir214]

No problem.

I've really never missed subqueries. But then, I never really used them much either, even on MSSQL or Oracle. I've always used related tables and various types of joins.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!