SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Victor: Jun 18, 2004

If you give me sample data (more rows than would be displayed in your desired output) and the desired output, I'll give you a SQL statement that works. Or I'll be really amusing in my attempt. :v:

# ? Jan 10, 2008 02:49

Adbot: ADBOT LOVES YOU

# ? May 3, 2024 16:31

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Victor posted:

If you give me sample data (more rows than would be displayed in your desired output) and the desired output, I'll give you a SQL statement that works. Or I'll be really amusing in my attempt.

Let's assume instead of summing the top 15 users, we'll just sum the top 5. That makes this test data considerably easier to type out.

code:

[b]fw_league[/b]
id    
-------------------------
1
2

[b]full_fw_user[/b]
fw_user_id    fw_league_id
---------------------------
1       	1
2		1
3		1
4		1
5		1
6		1
7		1
8		1
9		2
10		2
11		2
12		2
13		2
14		2
15		2
16		2
17		2
18		2
19		2
20		2

[b]fw_user_total[/b]
fw_user_id	points	num_correct
-------------------------------------
1		10		5
2		20		6
3		30		7
4		40		8
5		50		9
6		25		6
7		35		8
8		60		10
9		80		11
10	        30		6
11		50		7
12		10		2
13		45		8
14		20		3
15		70		8
16		40		5
17		15		3
18		0		0
19		35		5
20		30		5

So the output I want is -
fw_league_id	sum_points	sum_correct
---------------------------------------
1	      60+50+40+35+30    10+9+8+8+7
2	      80+50+45+70+40	11+7+8+8+5

So I need the top 5 scoring users (ordered first by points, then by num_correct) to be summed up for each league.

# ? Jan 10, 2008 16:26

Victor: Jun 18, 2004

cletus42o, RDBMS?

# ? Jan 10, 2008 17:11

Victor: Jun 18, 2004

SQL2005, with a few of my own CLR functions that should be obvious or irrelevant:

code:

with fw_league as (
    select  id = value
    from    dbo.RegexSplit('1,2', ',')
), full_fw_user as (
    select  fw_user_id = p.[0],
            fw_league_id  = p.[1]
    from    dbo.RegexSplit(dbo.RegexReplace('
        !fw_user_id    fw_league_id
        1       1
        2       1
        3       1
        4       1
        5       1
        6       1
        7       1
        8       1
        9       2
        10      2
        11      2
        12      2
        13      2
        14      2
        15      2
        16      2
        17      2
        18      2
        19      2
        20      2
        '
        , '(?m:^(\s*!.*)?\r\n)|(?-m:\s+$)|(?m:^\s+)', ''), '\r\n') lines
    cross apply dbo.RegexSplit(value, '\s+') cols
    pivot (max(cols.value) for cols.idx in ([0], [1])) p
),  fw_user_total as (
    select  fw_user_id = p.[0],
            points  = cast(p.[1] as int),
            num_correct = cast(p.[2] as int)
    from    dbo.RegexSplit(dbo.RegexReplace('
        !fw_user_id points  num_correct
        1       10      5
        2       20      6
        3       30      7
        4       40      8
        5       50      9
        6       25      6
        7       35      8
        8       60      10
        9       80      11
        10      30      6
        11      50      7
        12      10      2
        13      45      8
        14      20      3
        15      70      8
        16      40      5
        17      15      3
        18      0       0
        19      35      5
        20      30      5
        '
        , '(?m:^(\s*!.*)?\r\n)|(?-m:\s+$)|(?m:^\s+)', ''), '\r\n') lines
    cross apply dbo.RegexSplit(value, '\s+') cols
    pivot (max(cols.value) for cols.idx in ([0], [1], [2])) p
), [b]/*** start reading here ***/[/b] numbered as (
    select  row_number = row_number() over (partition by fw_league_id order by points desc),
            points,
            fw_league_id,
            num_correct
    from    full_fw_user u
    inner join fw_user_total t on t.fw_user_id = u.fw_user_id
)
select  fw_league_id, 
        x_points = replace(dbo.JoinWithPipe(points), '|', '+'),
        sum_points = sum(points),
        x_correct = replace(dbo.JoinWithPipe(num_correct), '|', '+'),
        sum_correct = sum(num_correct)
from    numbered
where   row_number <= 5
group by fw_league_id

Results:

code:

fw_league_id   x_points         sum_points   x_correct    sum_correct
------------   --------------   ----------   ----------   -----------
1              60+50+40+35+30   215	     10+9+8+8+7   42
2              80+70+50+45+40   285	     11+8+7+8+5   39

Victor fucked around with this message at 17:22 on Jan 10, 2008

# ? Jan 10, 2008 17:19

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Nice, thanks. I will see if I can get that to work. Took me a second to realize what was going on until I saw the "start reading here."

edit - Actually, I'm still not really sure how to use this. Could you show me what I should only need to stick into QA to get the proper results, if you don't mind? SQL 2000, by the way. I also don't actually need a string showing "x+x+x+x+x", I was only doing that to show you what numbers were being summed up.

Okay, I think I have it all properly entered.. but it's getting caught on row_number() - what function is this? Is there a 2000 equivalent?

cletus42o fucked around with this message at 17:33 on Jan 10, 2008

# ? Jan 10, 2008 17:21

Victor: Jun 18, 2004

I was lazy and didn't generate create table statements and related annoyances. See http://luke.breuer.com/time/item/SQL_2005_tables_from_strings/201.aspx .

# ? Jan 10, 2008 17:25

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

So I was able to write this based on some advice I got elsewhere.. (couldn't get your SQL to work, Victor, since row_number() doesn't seem to have any equivalent in 2000 - thanks though!)

code:

SELECT
	fw_league_id,
	SUM(points) AS total_points,
	SUM(num_correct) AS total_num_correct
FROM
	(
		SELECT
			u.name,
			u.fw_league_id,
			d.ranknum,
			t.points,
			t.num_correct
		FROM
			full_fw_user u
		INNER JOIN
			fw_user_total t
		ON
			u.fw_user_id = t.fw_user_id
		INNER JOIN
			(
				SELECT
					a.fw_user_id,
					COUNT(*) AS ranknum
				FROM
					(
						SELECT
							u.fw_user_id,
							u.fw_league_id,
							t.points,
							t.num_correct
						FROM
							full_fw_user u
						INNER JOIN
							fw_user_total t
						ON
							t.fw_user_id = u.fw_user_id
					) AS a
				INNER JOIN
					(
						SELECT
							u.fw_user_id,
							u.fw_league_id,
							t.points,
							t.num_correct
						FROM
							full_fw_user u
						INNER JOIN
							fw_user_total t
						ON
							t.fw_user_id = u.fw_user_id
					) AS b
				ON
					(
							a.fw_league_id = b.fw_league_id
						AND
							[b]a.points <= b.points[/b]
					)
				GROUP BY
					a.fw_user_id
				HAVING COUNT(*) <= 15
			) AS d
		ON
			u.fw_user_id = d .fw_user_id
	) AS t
GROUP BY
	fw_league_id

This works, but it's only ordering by POINTS DESC. I would like for it to order first by points DESC, then by num_correct DESC. The part that's "ordering" it by points is bolded. Anyone have any ideas?

# ? Jan 10, 2008 21:11

Victor: Jun 18, 2004

code:

select  fw_league_id, 
        sum_points = sum(points),
        sum_correct = sum(num_correct)
from (
    select  u.fw_user_id,
            u.fw_league_id,
            t.points,
            t.num_correct,
            count = (
                select  count(*)
                from    fw_user_total x
                inner join full_fw_user y on y.fw_user_id = x.fw_user_id
                where   y.fw_league_id = u.fw_league_id
                    and
                        (
                                x.points > t.points 
                            or  
                                x.points = t.points and x.fw_user_id < t.fw_user_id
                        )
            )
    from    full_fw_user u
    inner join fw_user_total t on t.fw_user_id = u.fw_user_id
) t
where   count < 5
group by fw_league_id

# ? Jan 10, 2008 22:35

Victor: Jun 18, 2004

The above can be simplified -- I'm too lazy and tired.

# ? Jan 10, 2008 22:48

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Victor posted:

The above can be simplified -- I'm too lazy and tired.

Thanks for the help, dude! I appreciate it.

# ? Jan 10, 2008 23:02

SuddenExpire: Jun 29, 2005; expired...

SuddenExpire fucked around with this message at 03:22 on Mar 20, 2015

# ? Jan 12, 2008 09:10

Victor: Jun 18, 2004

Do you have an index on `DATE`? How about `status`?

# ? Jan 12, 2008 11:10

SuddenExpire: Jun 29, 2005; expired...

SuddenExpire fucked around with this message at 03:22 on Mar 20, 2015

# ? Jan 12, 2008 14:24

Victor: Jun 18, 2004

Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).

# ? Jan 12, 2008 14:26

kalleboo: Jan 13, 2001; Hj�lp

Victor posted:

Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).

Even with separate indexes it shouldn't take 4-5 seconds, something must be wrong.

# ? Jan 12, 2008 16:53

Victor: Jun 18, 2004

We don't know the server's specs.

# ? Jan 12, 2008 16:54

Ardhanari: Mar 23, 2006

Replace the last two WHERE conditions with a BETWEEN?

# ? Jan 12, 2008 17:22

benisntfunny: Dec 2, 2004; I'm Perfect.

Ardhanari posted:

Replace the last two WHERE conditions with a BETWEEN?

That really shouldn't make much of a difference at all.. that query is nothing.. sounds like your machine must just really be a piece of poo poo.

Sort of like when I had Dreamhost hosting my MySQL databases...

# ? Jan 12, 2008 18:24

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

imBen posted:

That really shouldn't make much of a difference at all.. that query is nothing.. sounds like your machine must just really be a piece of poo poo.

Sort of like when I had Dreamhost hosting my MySQL databases...

Or it's misconfigured (using my-small.cnf or something similar).

# ? Jan 12, 2008 20:28

benisntfunny: Dec 2, 2004; I'm Perfect.

deimos posted:

Or it's misconfigured (using my-small.cnf or something similar).

There's also this... since I don't know the exact data setup...
http://www.forbiddenweb.org/topic/12493/index.html

or..
Also to be sure it's actually using the index you could force it on there.
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
I've run into trouble with some of my SQL Server queries being "optimized" to run without utilizing an index. I'm sure this problem isn't exclusive to Microsoft.

# ? Jan 12, 2008 20:44

Victor: Jun 18, 2004

Maybe the planets aren't aligned?

# ? Jan 12, 2008 20:58

SuddenExpire: Jun 29, 2005; expired...

Victor posted:

Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).

SuddenExpire fucked around with this message at 00:18 on Oct 7, 2016

# ? Jan 12, 2008 23:02

kalleboo: Jan 13, 2001; Hj�lp

Victor posted:

Maybe the planets aren't aligned?

Uh oh http://www.pcpro.co.uk/news/153417/solar-flares-threaten-gps.html

# ? Jan 14, 2008 14:56

increased: Feb 20, 2001

MySQL Fulltext Search Apostrophes Question

I'm using a MATCH AGAINST search. I want the search phrase "pete" to match the stored title "Pete's Nougat". Right now only "pete's" matches "Pete's Nougat". Is this possible?

increased fucked around with this message at 21:27 on Jan 14, 2008

# ? Jan 14, 2008 21:15

thefncrow: Mar 14, 2001

OK, I've got an xquery on SQL 2005 question here. I'm working with an XML structure that we store in a SQL Server 2005 database. The XML structure stored is large, and prohibitive to display all at once via an XSLT transformation, which is the current display method. I'm looking to implement filtering, ordering, and paging on the XML data.

I've got a basic XML structure that's something like this(though I've changed certain details):

code:

<BILL BillID="12345678">
  <Item ID="1">
    <N1 type="Sender">Shipper Name</N1>
    <N2 type="Sender">Shipper Street Address</N2>
    <N3 type="Sender">
      <City>ShipperCity</City>
      <State>SH</State>
      <Postal>12345</Postal>
    </N3>
    <N1 type="Receiver">Receiver Name</N1>
    <N2 type="Receiver">Receiver Street Address</N2>
    <N3 type="Receiver">
      <City>ReceiverCity</City>
      <State>RC</State>
      <Postal>23456</Postal>
    </N3>
    <ItemCode>8675309</ItemCode>
    <Date>01/02/2003</Date>
    <Weight type="lb">3</Weight>
    <Price currency="USD">72.29</Price>
  </Item>
  <Item ID="2">
  ....
   and so on
  ....
</BILL>

Now then, I've written a series of scalar-valued functions that return XML to my application that contain a filtered subset of the original XML, an ordered subset of the original XML, or just a count-limited subset of the XML(say, the items to appear on Page 11 when we use 25 items per page). Performance is going to be an issue, since these things could all potentially be chained together(ie, give me Page 11(at 25 items a page) of all records coming out of Dallas,TX sorted by price in descending order). The paging function is really very speedy, considering the size of the input, and doesn't present that big of a problem. I haven't even gotten to testing the filtering, though, because the ordering functions are a really nasty problem.

For performance testing, I ran my functions against a rather large XML structure in our database, which contains over 25,000(25,057 to be exact) of those Item tags. This is an especially large structure, but it's not necessarily out of touch with the largest structures in production. My paging function can fetch this large structure and return, say, items 776-800 in about 25 seconds. However, if I were to need those same items returned, but with the data sorted by Weight, the OrderByWeight function takes six and a half minutes to execute the following code:

code:

<BILL BillID="{/BILL/@BillID}"> 
{
 for $item in /BILL/Item 
 order by $item/Weight[1] ascending 
 return $item
}
</BILL>

(The header and footer tags are just there so that I can maintain my basic XML structure from function call to function call and chain them as appropriate, ie Page(Order(Filter(OriginalXML))). Once again, this is an approximation of the code I have, but the FLWOR expression is almost exactly what I'm using outside of having changed the name of the tags)

Worse yet, I can apply other order methods that have been asked for, like sorting by "<City>, <State>" on the shipper end. I tried to apply this sort to my large XML file before I went for lunch, thinking I'd check the execution time when I returned. 30 minutes later, it was still executing, at which point I cancelled it. That code was very similar to the above, just like this instead:

code:

<BILL BillID="{/BILL/@BillID}">
{
  for $item in /BILL/Item
  order by $item/N3[@type="Sender"][1]/City[1] ascending, $item/N3[@type="Sender"][1]/State[1] ascending
  return $item
}
</BILL>

So, clearly, using the order by clause is going to be prohibitively expensive. Is there really anything else I can use in XQuery or otherwise for ordering within XML that might be faster? This page is going to end up being customer-facing, and so asking a customer to hold on for 20 minutes while we retrieve page 3 of their data sorted and filtered by some criteria is not really a realistic option, not to mention that this improvement was driven by the sluggish response of those large XML structures to begin with.

# ? Jan 14, 2008 22:03

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Filash posted:

MySQL Fulltext Search Apostrophes Question

I'm using a MATCH AGAINST search. I want the search phrase "pete" to match the stored title "Pete's Nougat". Right now only "pete's" matches "Pete's Nougat". Is this possible?

I'm finding fulltext to be less than useful by itself. If you do it in boolean mode, you can add a * to it to include the other words - but only if you use single words! If you want to search for a literal string you can't add wildcards to it. Sigh.

Also, the problem may not be that it's not finding Pete's Nougat, but rather that "Pete" might be too short a search phrase for the fulltext. Change the "ft_min_word_len" setting; I believe the default is 4. Try that first before you try changing to a boolean search.

# ? Jan 14, 2008 23:48

increased: Feb 20, 2001

Golbez posted:

I'm finding fulltext to be less than useful by itself...

Change the "ft_min_word_len" setting...

I think fulltext has a lot of problems too. Fortunately this solution is only going to be in place for a few months.

ft_min_word_len is 3.

I still wonder if there's a way to make it so that "pete" finds "Pete's" by changing the MySQL config (and not by massaging the string somehow).

# ? Jan 15, 2008 00:06

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a PHP script that, at the very top of the script, makes a MySQL query that grabs a bit of data from the database, and makes that data global. So it looks like this:

php:

<?
$query = "some query";
global $data;
$data = mysql_query($query);
?>

Then the rest of the script has a bunch of functions that use that global mysql data. I have it done this way because I don't want the script to hit the database a bunch of times for the exact same data.

When the functions use the data, they do it with mysql_fetch_array() and a while() loop. At the end of each of those loops, I have "mysql_data_seek($data, 0);", but it still thinks each one, except for the first, is empty. What am I missing?

# ? Jan 15, 2008 18:37

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

It seems like you have it set up right, but maybe there's a flaw in your code you are overlooking. What do your while loops and calls to mysql_data_seek() look like.

Also, did you mean each loop, except the first is trying to loop thru empty result sets, or that the result sets are empty except for the first row.

# ? Jan 15, 2008 22:17

rocketrobot: Jul 11, 2003

I've got a table with data like this:

code:

ID    | date
--------------
1     | 1/1/2001
1     | 1/9/2001
1     | 1/11/2001
2     | 1/1/2001
2     | 1/5/2001
2     | 1/16/2001
3     | 1/1/2001
3     | 1/3/2001
3     | 1/6/2001
3     | 1/12/2001

I am trying to write a select statement with a field that has the rule that in any one ID value if any subsequent date is greater than 7 days from the preceding date then the returned value should be 1, else the value is 0 (bit field).

So that part would be something like:

code:

CASE
    WHEN <insert function here> > 7 THEN 1
    ELSE 0
END AS Valid

So, with that rule in the above data, the results should look like

code:

ID    | Valid
--------------
1     | 1
2     | 1
3     | 0

I was trying to do this with a datediff between Min and Max values until I discovered there can be more than two date entries for any one ID. So then I tried setting up some stuff using ADO and making a record set that ordered the fields chronologically and then used move.next to loop through them, but I'm not sure that's going to work either. Anyone have an idea on how to build this rule into a SQL statement? I'm at a bit of a loss at the moment.

rocketrobot fucked around with this message at 23:23 on Jan 15, 2008

# ? Jan 15, 2008 23:13

rocketrobot: Jul 11, 2003

Ok, so I got it. Had to create a unique ID field for each row, but this is how it went down:

code:

SELECT	s3.ID, 
s3.Date, 
s4.Date,
DATEDIFF(d,s3.Date,s4.Date) DaysSince,
CASE 
    WHEN DATEDIFF(d,s3.Date,s4.Date) > 7 THEN 1 
    ELSE 0 
END as Valid

FROM 
	(SELECT	s1.ID, 
		s1.Date, 
		MIN(s2.DateID) as DateID
	FROM dbo.BaseTable s1
		INNER JOIN dbo.BaseTable s2 
                      ON s2.ID = s1.ID 
		      AND s2.Date > s1.Date
	GROUP BY s1.ID, 
		s1.Date) s3

INNER JOIN dbo.BaseTable s4 ON
	s4.DateID = s3.DateID
ORDER BY s3.ID

That really sucked and took all day.

# ? Jan 16, 2008 00:19

Xae: Jan 19, 2005

Use a cursor: This is getting pretty sophisticated, you can probably use some SQL hack to do it, but in the long run doing it "the right way" will be best.

# ? Jan 16, 2008 00:20

rocketrobot: Jul 11, 2003

Xae posted:

Use a cursor: This is getting pretty sophisticated, you can probably use some SQL hack to do it, but in the long run doing it "the right way" will be best.

Our DBAs don't let us use cursors because they suck. I suppose I could have forced the issue and made them let me use one, but this works.

# ? Jan 16, 2008 00:41

Xae: Jan 19, 2005

Get your DBAs fired. They are worthless and are providing nothing of use to your organization. They should be fired and replaced immediately.

I'm not joking. I'm too lazy to check, but I would wager that query has an implicit cursor in it.

If they won't fire them switch jobs, you're crippling your development as a programmer, unless you really like it there or something...

Xae fucked around with this message at 02:04 on Jan 16, 2008

# ? Jan 16, 2008 02:02

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Filash posted:

I think fulltext has a lot of problems too. Fortunately this solution is only going to be in place for a few months.

ft_min_word_len is 3.

I still wonder if there's a way to make it so that "pete" finds "Pete's" by changing the MySQL config (and not by massaging the string somehow).

Elaborate please, are they getting rid of/drastically fixing fulltext in a few months?

# ? Jan 16, 2008 02:58

rocketrobot: Jul 11, 2003

Xae posted:

Get your DBAs fired. They are worthless and are providing nothing of use to your organization. They should be fired and replaced immediately.

I'm not joking. I'm too lazy to check, but I would wager that query has an implicit cursor in it.

If they won't fire them switch jobs, you're crippling your development as a programmer, unless you really like it there or something...

Haha, no, I'm getting out some time this year. The whole place is hosed.

# ? Jan 16, 2008 04:16

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

I have a general SQL table design question.

Let's say I have a table that stores "application events". The table needs to refer to the source of the event, and these sources may be of various different types, from various unrelated sub-systems of the application. E.g.

- Forum thread X was updated
- File Y was uploaded
- User Z was added

I'm trying to work out the best way of designing a table to store the reference to the source, and using a FK constraint as well (so that deleting the source of the event would also delete the event).

Option 1) 3rd-normal form ahoy!
-----------------------------

code:

table: "event"
event_id | event_time

table: forum_event
event_id (FK on event.event_id) | forum_thread_id 

table: file_event
event_id (FK on event.event_id) | file_id

table: user_event
event_id (FK on event.event_id) | user_id

Four tables. One holds the "common" information about the event, and the others hold the "specific" information about that event.

Pros:
+More space efficient (to an extent)
+More correct database normalization
Cons:
-Suckier to work with (requires JOINs, more complex collation code)
-Small performance hit from the FK constraints on event.event_id
-A new table is required to add a new type.

Option 2) Use a single table:
-----------------------------

code:

table: "event"
event_id | event_time | forum_thread_id | file_id | user_id

Each row contains an event_id/event_time, and exactly ONE of "forum_thread_id", "file_id", and "user_id" will ever be non-NULL.

Pros:
+Simple to understand
+Means only searching one table, less schema management
+A new column is all that's required to add a new type.
Cons:
-Should implement a row "CHECK" to ensure that exactly 1 of the event-specific fields was chosen.
-A little more code required to identify which type of event this is.

Option 3) Store a "type" and ID
-----------------------------

code:

table: "event"
event_id | event_time | type | id

Here, "type" would be a string like "forum", "file", or "user". "id" would be the ID of that forum, file or user.

Pros:
+Succinct and simple
Cons:
-Not possible to use FK constraints on "id".
-Alarmingly like the "Entity-Attribute-Value" anti-pattern.

In the past I've implemented Option 1, like a good little database normalizer. However the clunkiness of doing so has led me to want to implement Option 2. The rest of my team (not very DB-design-savvy) is advocating Option 3, which to me is right out because it would take god himself to pry FK constraints from my dead hands.

Has anyone had any experience on what is the best approach?

# ? Jan 16, 2008 05:28

Xae: Jan 19, 2005

If you care about your data option 1 is best.

If not option 2 works decent enough.

# ? Jan 16, 2008 05:36

benisntfunny: Dec 2, 2004; I'm Perfect.

minato posted:

Option 2) Use a single table:
-----------------------------
code:
table: "event"
event_id | event_time | forum_thread_id | file_id | user_id
Each row contains an event_id/event_time, and exactly ONE of "forum_thread_id", "file_id", and "user_id" will ever be non-NULL.

Pros:
+Simple to understand
+Means only searching one table, less schema management
+A new column is all that's required to add a new type.
Cons:
-Should implement a row "CHECK" to ensure that exactly 1 of the event-specific fields was chosen.
-A little more code required to identify which type of event this is.

Use option 2 with an index on event_time... you might choose other indexes but at least do that..

Option 2 is basically how my company does logging enterprise wide. Not that everything we do is right... lots of times it's not.. but it seems to work pretty good overall.

# ? Jan 16, 2008 05:52

Adbot: ADBOT LOVES YOU

# ? May 3, 2024 16:31

m5: Oct 1, 2001

As it happens, I implemented an event log system almost exactly like that. We've got over 300 different types of events, and maybe six or eight different kinds of event "targets".

One basic caveat: I would seriously recommend that you reconsider wanting to builid in those FK constraints. They'll really load things down. In our system, we've got literally millions and millions of events - every day's batch processing causes many hundreds of thousands of new ones to be logged. Those constraints will make deletes take forever if you're not careful.

Also, if your event log is anything like an audit trail, why would you want entries in it to go away? Things that happened don't "un-happen" when the target is removed from the system.

Our events track the target info, the "actor" responsible (if any), the event type, the timestamp, and disposition information (our events can generate audit email, and we have to keep track of whether an event has been thusly processed). There's a head table to keep track of common attributes (like you said), and then auxilliary tables by event type for extra attributes. Our events are defined in an XML file, and some code generates Java classes and SQL code for maintaining stuff. The generation happens automatically as part of the build.

We used to keep FK constraints on the aux tables back to the head table, but that was causing us real problems. We've never kept FK constraints back to target objects.

# ? Jan 16, 2008 13:30

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »