MIME-Version: 1.0
From: Misa Simic <misa.simic@gmail.com>
Date: Mon, 12 Dec 2011 01:36:24 -0800
Message-ID: <-8561915203048652361@unknownmsgid>
Subject: Re: Subselects to Joins? Or: how to design phone calls database
To: Mario Splivalo <mario.splivalo@megafon.hr>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8

I think its definitely better to split phone number in calls table on 2
or even 3 parts... (Country prefix, carrier/area prefix, number)

Though maybe better design would be 3th table with full number as pk:
PhoneNumbers (number, country prefix, optionally carrier/area prefix,
rest of number)

Then you can join calls to phonenumbers on full number string then join
countries on country prefix...

Kind Regards,

Misa

Sent from my Windows Phone From: Mario Splivalo
Sent: 10 December 2011 23:27
To: pgsql-sql@postgresql.org
Subject: [SQL] Subselects to Joins? Or: how to design phone calls
database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:

CREATE TABLE cdr (
	call_id serial,
	phone_number text
);

And I have a table with country call prefixes, that looks like this:

CREATE TABLE prefixes (
	prefix text,
	country text
);

And now some test data:

INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');


Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:

SELECT
	call_id,
	phone_number,
	(SELECT
		country
	FROM
		prefixes
	WHERE	
		calls.phone_number LIKE prefix || '%'
	ORDER BY
		length(prefix) DESC LIMIT 1
	) AS country
FROM calls;


Is there a way I could use join here? I can do something like:

SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'

but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).

Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?

	Mario

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql