Search Postgresql Archives

phone database schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux