Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. The table represents completed tasks, the columns are:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

Reference is used when the customer provides their own ID for the task, typically this is for example "THR#000123123" or "IX#01212". It is always in the format "ABC#123".

Now, I need to categorise these into multiple arbitrary categories and subcategories based on only the type and reference fields:

  1. Chargeable (type = 3)

    1. By company IX (reference like "IX#%")

    2. All other Chargeable

  2. Non-chargeable (type <> 3)

    1. By company THR (reference like "THR#%")

      1. Return-to-base (type = 2)

      2. On-site (type = 1)

    2. Return to base (type = 2)

    3. All other non-Chargeable

A task can only belong to one category, so if a task for company THR was "return-to-base", it must only be under category 2.1.1, not 2.2. I realise these categories seem very arbitrary but this structure represents the most useful break-down of almost any selection of tasks from the table.

My question is, is it possible to create a view that contains the TaskID and its category? Or should I just implement this logic in the front-end after selecting every TaskID, Type and Reference?

share|improve this question
    
Are IX and THR the only companies which are special in categorising? –  dezso Sep 27 '12 at 6:52
    
@dezso: Yes, IX and THR are the only "important" companies. There are others as well but are mostly irrelevant when looking at the bigger picture. –  dreamlax Sep 27 '12 at 9:15
    
If Rebook (type = 5) is a subcategory of Chargeable (type = 3), does that mean that a TaskID can be assigned more than one type (3 and 5 in this case)? –  Andriy M Sep 27 '12 at 11:22
    
@AndriyM: Sorry that's a typo, good spotting. Each task only has one type, in this case, "Rebook" implies "non-chargeable". I'll edit the question. Not quite sure how that got there... –  dreamlax Sep 27 '12 at 11:45

2 Answers 2

up vote 4 down vote accepted

Ok, so my answer here is conditioned on the following assumptions:

  1. This categorization needs to be relatively stable and
  2. It may be re-used in various ways, and
  3. It needs to perform well.

The obvious, simple answer is to use table methods in PostgreSQL. What you do is create a SQL language function which does not hit the table and returns the value you want. Make it immutable so you can index the output if you want to query against it, etc. Note the specific rules aren't really clear from your post but this should get you started.

CREATE OR REPLACE FUNCTION subcategory(task) -- task is table name
RETURNS text LANGUAGE sql IMMUTABLE AS
$$
    SELECT CASE WHEN $1.type = 3 THEN
                CASE WHEN $I.reference LIKE 'IX#%' 
                     THEN 'Company IX Chargeable'
                     ELSE 'Other Chargeable'
                END
                WHEN $1.reference LIKE 'IX#%' THEN 
                CASE WHEN $1.type = 5 THEN 'Company IX Rebool'
                     ELSE 'Company IX Other'
                END
                WHEN $1.reference LIKE 'THR#%' AND $1.type IN (1, 2) THEN
                CASE WHEN $1.type = 2 THEN 'THR Return to Base'
                     WHEN $1.type = 1 THEN 'THR On Site'
                END
                WHEN $1.type = 2 THEN 'Return to Base'
                ELSE 'Other Nonchargeable'
           END;
$$;

You can then query this using:

SELECT t.subcategory FROM task t;

Note that the table name is non-optional here. The parser converts this to:

SELECT subcategory(t) FROM task t;

You can use this however in any part of the select statement including the where clause. If it proves slow, you can add cost estimates (but I think this should be fast), and you can even index the output using PostgreSQL's functional indexes.

On the performance side, LIKE and substring() don't seem to be significantly different:

postgres=# select count(*) from generate_series(1, 10000000);
  count   
----------
 10000000
(1 row)

Time: 9007.618 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count  
---------
 1111112
(1 row)

Time: 13653.000 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count  
---------
 1111112
(1 row)

Time: 16681.860 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count  
---------
 1111112
(1 row)

Time: 17163.470 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count  
---------
 1111112
(1 row)

Time: 17052.004 ms

So sometimes one is faster than the other, but they don't seem out of line with eachother.

share|improve this answer
    
Wouldn't using substr instead of LIKE be faster? –  dezso Sep 27 '12 at 10:50
    
On an other note, I think you mean the exact opposite: ' which does not hit the table' –  dezso Sep 27 '12 at 10:51
    
No, I mean that. The function I posted operates on the type, not on the table. Therefore executing the function doesn't require actual disk I/O in itself. This is important for performance and flexibility. For example you can safely index the output because the input is tied to the data in the tuple not in the table per se. Will test the performance on the other side. –  Chris Travers Sep 27 '12 at 11:13
    
This is looking promising... I've been trying to maintain some sort of "hierarchical" system just as the categorisation looks, but using a "flat" approach would be much simpler. I'll fiddle around with this and let you know how I get on. –  dreamlax Sep 27 '12 at 11:51
    
You can nest the case statements however you like to provide something a little more hierarchical. Also for more on table methods see ledgersmbdev.blogspot.com/2012/08/… –  Chris Travers Sep 27 '12 at 11:55

Yes, you could do this in SQL, but personally I wouldn't. You will need to use string functions quite extensively which are much more expensive from a resource perspective, than doing it in the front end code. I would expect a front-end solution to this problem to run significantly faster.

Of course this has caveats depending on your architecture bottlenecks and the amount of rows. I did have a similar tokenization issue on a table that contained just under a billion rows and after testing both SQL only and .NET only solutions, the final answer came from a SQL FullText/.NET hybrid.

share|improve this answer
    
Well, I don't anticipate anywhere near a billion rows, probably in the low millions actually. I will fiddle around with .NET but I can't seem to get npgsql working on Windows Server 2008 R2. I'll persevere though as .NET is the ideal environment for the rest of the applications. –  dreamlax Sep 27 '12 at 9:13
    
.NET, I'd really the ideal environment for string manipulation as well, where possible –  Pete Carter Sep 27 '12 at 9:19
2  
I don't think it's that bad. and if you really need more, there is always pl/perl.... adding an answer. –  Chris Travers Sep 27 '12 at 10:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.