IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Extra flag or an additional table?
We have a database question here...

We're polling some (arbitrary) data. The data can be integers or stings. So we put it into an Oracle database, in the form of "timestamp, value, string_value". Sometimes we get a timeout. We need to create a row in the table for timeout as well. Should I add a flag to each row of the table, or should I keep timeouts in separate table, one w/o value fields?

My gut feeling says "separate table", on the groubds of normalization and not having any redundant/unused data. The easiest thing is to add a flag field. Which one is right?
--

We have only 2 things to worry about: That
things will never get back to normal, and that they already have.
New I'd use a flag.
On the grounds that it's not abnormal if you think of the table as "polls" and not "polled data".

Which answer is "right" depends on what sort of management queries you see yourself running in the future. If you're going to be asking questions like "what's our timeout-to-success ratio?" you might want the flag. If you want faster summaries of only valid data, you might want the separate table. How fast is the poll compared to the overhead of maintaining parallel updating tables?

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New Queries are somewhat unpredictable
We're making a general/purpose tool here.

I don't see how ratio is faster with single table - it's simple "select count() from" versus "select count() from ... where ...". Also, I thought that updating 2 smaller tables is faster than updating 1 large one.

As to "polls" and not "polled data"... Could you elaborate on that? Not sure I understand.

The only query that is faster on single table, IMHO, is the one that has to merge the tables. Actually, I wonder.. How the hell do I write a query that merges them, while retaining, for each record, the origin of it? IOW, how do I get from 2 tables to one with a flag? I guess my lack of SQL experience is showing.

--

We have only 2 things to worry about: That
things will never get back to normal, and that they already have.
New It's purely a design decision IMO--look to the future.
> I don't see how ratio is faster with single table
> - it's simple "select count() from" versus
> "select count() from ... where ...".

The "where" is another decision that needs to be made. Depends on the DB if that's faster or not for your dataset.

> Also, I thought that updating 2 smaller tables
> is faster than updating 1 large one.

Again, depends on the size of the tables and the DB. I'm not enough of an Oracle expert to answer that for your dataset. I would have thought opening and updating a single row would be faster than 2 any day...but I'm not a low-level DB expert. I'd test each one and see which is faster.

> As to "polls" and not "polled data"...
> Could you elaborate on that? Not sure I understand.

I'm just saying that often an object seems redundant only because of the label we put on it. If you look at your dataset as a list of "successful poll values", then the timeouts look out of place. But if you look at it as a list of "poll results (successful or not)", then the timeouts attain equal "status" with the other data. If you were going to normalize this table the "idealized" way, you'd have a master index of timestamps in one, values in a second, and timeouts in a third. That's true normalization: sharing what's similar and pulling out what's different. Yuck. Two tables is, to me, just as unappealing as three when you're talking about one bit of data, unless you see some point in the future where you'd add additional data regarding the duration or cause of the timeout.

The only query that is faster on single table, IMHO, is the one that has to merge the tables. Actually, I wonder.. How the hell do I write a query that merges them, while retaining, for each record, the origin of it? IOW, how do I get from 2 tables to one with a flag? I guess my lack of SQL experience is showing.


Use a UNION query. That will get ugly for the same reasons it's ugly using two tables instead of three.

MSAccess example (hoping it's standard):
SELECT Timestamp, Name, Value, False AS IsTimeout FROM Polls
UNION
SELECT Timestamp, Null AS Name, Null AS Value, True AS Timeout FROM Timeouts

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New I see. Thank you.
Your example with 3 tables seems to do it for me. Ouch indeed.
--

We have only 2 things to worry about: That
things will never get back to normal, and that they already have.
New Two or Three Extra columns, more than a flag...
The way we typically do this at our site is to have a "Start Time" column, an "End Time" column, and a timeout flag.

The reason for the start/end times is that you can subtract start from end and find out how long the poll took (usually in milliseconds, but for some systems in millionths of a second).

The timeout flag tells you that the transaction ended because of a timeout.

The reason you want to do this is that your timeout value might change over time ( 30 seconds this month, 25 last month, 45 two months ago), or you might expand the system to multiple customers where each has his own timeout.

Reporting timeouts for a period of time (1 day, 1 week, 1 month) is now much easier, because you can use the start timestamp as a key value field in the time period (be sure to create indexes on start and end time).

Just my thoughts.
Expand Edited by gdaustin Nov. 7, 2002, 07:23:09 PM EST
New See? Told ya someone would want more data. :D

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New I vote flag field
Lots of skinny tables gets annoying IMO.
________________
oop.ismad.com
     Extra flag or an additional table? - (Arkadiy) - (7)
         I'd use a flag. - (tseliot) - (3)
             Queries are somewhat unpredictable - (Arkadiy) - (2)
                 It's purely a design decision IMO--look to the future. - (tseliot) - (1)
                     I see. Thank you. - (Arkadiy)
         Two or Three Extra columns, more than a flag... - (gdaustin) - (1)
             See? Told ya someone would want more data. :D -NT - (tseliot)
         I vote flag field - (tablizer)

I heartily endorse this event or product!
94 ms