Firstly, thank you very much to all advice that has been passed
through. These groups have thought me many new tricks and I wish all
of you the best.
Now, I'm trying to create a schema for mobile phone custodian and
billing system. Any advice would be greatly appreciated, I've
included a schema that I've been working on and some sample data.
Personally I feel this schema can be improved thoroughly but I need
some help in designing it.
-- start script --
-- mobile user --
create table mobile_user (
user_id serial not null,
first_name text,
last_name text,
department text,
section text,
CONSTRAINT mobile_user_pkey PRIMARY KEY (user_id)
);
INSERT INTO mobile_user(first_name, last_name, department, section)
VALUES ('fuser1', 'luser2', '106', 'driver');
-- mobile --
create table mobile_number (
mobile_no text not null,
sim_no text,
pin_code text,
puk_code text,
issue_date date,
return_date date,
status boolean,
CONSTRAINT mobile_number_pkey PRIMARY KEY(mobile_no)
);
INSERT INTO mobile_number(mobile_no, sim_no, pin_code, puk_code,
issue_date, status)
VALUES ('09455225998', 'X1255849', '0000', '0000', '2008-10-10','y');
-- device --
create table accessory (
accessory_id serial not null,
make text,
model text,
serial text,
price money DEFAULT '$0.00',
CONSTRAINT accessory_id_pkey PRIMARY KEY (accessory_id)
);
INSERT INTO accessory(make, model, serial, price)
VALUES ('NOKIA', 'N70', 1234, '151.00');
-- custodian --
create table mobile_custodian (
custodian_id serial not null,
user_id int references mobile_user (user_id),
mobile_no text references mobile_number (mobile_no),
accessory_id int references accessory (accessory_id),
issue_date date not null,
return_date date,
status boolean not null,
CONSTRAINT mobile_custodian_id_pkey PRIMARY KEY (custodian_id)
);
INSERT INTO mobile_custodian(user_id, mobile_no, accessory_id,
issue_date, status)
VALUES (1, '09455225998', 1, '2008-10-11', 'y');
-- billing --
create table bill_period (
bill_id text not null, -- eg. YYYY-MM
start_date timestamp,
end_date timestamp,
CONSTRAINT bill_id_pkey PRIMARY KEY(bill_id)
);
INSERT INTO bill_period(bill_id, start_date, end_date)
VALUES ('2008-07', '2008-06-30 00:00', '2008-08-03 23:59');
INSERT INTO bill_period(bill_id, start_date, end_date)
VALUES ('2008-08', '2008-08-04 00:00', '2008-08-31 23:59');
create table call (
call_id serial,
bill_id text references bill_period (bill_id),
mobile_no text references mobile_number (mobile_no),
datetime timestamp,
origin text,
destination text,
call_no text,
duration interval,
charge float,
CONSTRAINT call_id_pkey PRIMARY KEY(call_id)
);
INSERT INTO call (bill_id, mobile_no, datetime, origin, destination,
call_no, duration, charge)
VALUES ('2008-07', '09455225998', '2007-07-10 10:00', 'london',
'new york', '12345632', '0:12:05', 5.28);
INSERT INTO call (bill_id, mobile_no, datetime, origin, destination,
call_no, duration, charge)
VALUES ('2008-08', '09455225998', '2007-08-12 13:27', 'rome',
'canada', '325699845','0:15:57', 3.15);
-- Also, here's a query that I'm playing around with, which I think is
-- going to be used a lot to produce individual reports.
select
mobile_user.first_name,
mobile_user.last_name,
call.mobile_no,
call.origin,
call.destination,
call.call_no,
call.duration,
call.charge
FROM (call INNER JOIN mobile_custodian ON call.mobile_no =
mobile_custodian.mobile_no) INNER JOIN mobile_user ON
mobile_custodian.user_id = mobile_user.user_id;
-- end script --
first_name | last_name | mobile_no | origin | destination |
call_no | duration | charge
------------+-----------+-------------+--------+-------------+-----------+----------+--------
user1 | last1 | 09455225998 | rome | canada |
325699845 | 00:15:57 | 5.2
user1 | last1 | 09455225998 | london | new york |
12345632 | 00:12:05 | 5.2
(2 rows)
Many thanks in advance :)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
