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.

I have designed database tables of Mobile Phones & Tariffs.

It should support multiple mobile networks, sale type and the related affiliate.

Points (for operators) are vary depending on the selected phone, tarrif, network and sale Type.

Example Price Plans:

T-Mobile: Consumer Upgrade

  • Samsung Galaxy S3
  • Tarrif: Super One (Monthly Cost: $12.00)
  • This sale affiliate with 'Retailer One'
  • Total Point: 3.3

T-Mobile: Consumer New Connection

  • Samsung Galaxy S3
  • Tarrif: Super One (Monthly Cost: $35.00)
  • This sale affiliate with 'Retailer Two'
  • Total Point: 7.3

AT&T: 'Sim Only Deal' (Without Phone)

  • Tarrif: X-Deal Two (Monthly Cost: $18.00)
  • This sale affiliate with 'Retailer One'
  • Total Point: 10.0

See the tables design below, is this how it should be done? or how can it be improved?

Edit: My question was is the relationship design between phone, tarrif, network and saleType is ok? Basically operator can select a retailer which then select Mobile Network.. then select what type of sale (eg: consumer upgrade) and then select phone with related related tariffs. Depending what tarrifs and/or phone they have selected - operator gets number of points.

Database Design:

CREATE TABLE IF NOT EXISTS `affiliate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `affiliate` (`id`, `name`) VALUES
(1, 'Retailer One'),
(2, 'Retailer Two');

CREATE TABLE IF NOT EXISTS `network` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `network` (`id`, `name`) VALUES
(1, 'T-Mobile'),
(2, 'AT&T');

CREATE TABLE IF NOT EXISTS `network_saletype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `network_id` int(11) NOT NULL,
  `saletype_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `network_saletype` (`id`, `network_id`, `saletype_id`) VALUES
(1, 1, 1),
(2, 2, 3);

CREATE TABLE IF NOT EXISTS `phone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `phone` (`id`, `name`, `description`) VALUES
(1, 'Samsung Galaxy S3', ' Quad-core, NFC, AMOLED'),
(2, 'Apple iPhone 4S', 'A5 chip, 8MP camera, Siri voice');

CREATE TABLE IF NOT EXISTS `phone_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone_id` int(11) NOT NULL,
  `tarrif_id` int(11) NOT NULL,
  `affilicate_id` int(11) NOT NULL,
  `point` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `phone_points` (`id`, `phone_id`, `tarrif_id`, `affilicate_id`, `point`) VALUES
(1, 1, 1, 1, 3.3),
(2, 1, 2, 2, 7.3);

CREATE TABLE IF NOT EXISTS `saletype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `saletype` (`id`, `name`) VALUES
(1, 'Consumer Upgrade'),
(2, 'Consumer New Connection'),
(3, 'Sim-Only Deal');


CREATE TABLE IF NOT EXISTS `tariff_point` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tarrif_id` int(11) NOT NULL,
  `affilicate_id` int(11) NOT NULL,
  `point` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `tariff_point` (`id`, `tarrif_id`, `affilicate_id`, `point`) VALUES
(3, 3, 1, 10);

CREATE TABLE IF NOT EXISTS `tarrif` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `network_saletype_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `tariff_duration` int(11) NOT NULL,
  `monthly_cost` decimal(6,2) NOT NULL,
  `description` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `tarrif` (`id`, `network_saletype_id`, `name`, `tariff_duration`, `monthly_cost`, `description`) VALUES
(1, 1, 'Super One', 12, '12.00', '200 Mins, 400 Texts, 500MB Internet'),
(2, 1, 'Super One', 12, '35.00', '200 Mins, 400 Texts, 500MB Internet'),
(3, 2, 'X-Deal Two', 12, '18.00', '');

Result

mysql> select * from network;
+----+----------+
| id | name     |
+----+----------+
|  1 | T-Mobile |
|  2 | AT&T     |
+----+----------+

mysql> select * from affiliate;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Retailer One |
|  2 | Retailer Two |
+----+--------------+

mysql> select * from saletype;
+----+-------------------------+
| id | name                    |
+----+-------------------------+
|  1 | Consumer Upgrade        |
|  2 | Consumer New Connection |
|  3 | Sim-Only Deal           |
+----+-------------------------+

mysql> select * from network_saletype;
+----+------------+-------------+
| id | network_id | saletype_id |
+----+------------+-------------+
|  1 |          1 |           1 |
|  2 |          2 |           3 |
+----+------------+-------------+


  mysql> select * from tarrif;
+----+---------------------+------------+-----------------+--------------+-------------------------------------+
| id | network_saletype_id | name       | tariff_duration | monthly_cost | description                         |
+----+---------------------+------------+-----------------+--------------+-------------------------------------+
|  1 |                   1 | Super One  |              12 |        12.00 | 200 Mins, 400 Texts, 500MB Internet |
|  2 |                   1 | Super One  |              12 |        35.00 | 200 Mins, 400 Texts, 500MB Internet |
|  3 |                   2 | X-Deal Two |              12 |        18.00 |                                     |
+----+---------------------+------------+-----------------+--------------+-------------------------------------+

mysql> select * from  tariff_point;
+----+-----------+---------------+-------+
| id | tarrif_id | affilicate_id | point |
+----+-----------+---------------+-------+
|  3 |         3 |             1 |    10 |
+----+-----------+---------------+-------+

mysql> select * from  phone;
+----+-------------------+---------------------------------+
| id | name              | description                     |
+----+-------------------+---------------------------------+
|  1 | Samsung Galaxy S3 |  Quad-core, NFC, AMOLED         |
|  2 | Apple iPhone 4S   | A5 chip, 8MP camera, Siri voice |
+----+-------------------+---------------------------------+

mysql> select * from phone_points;
+----+----------+-----------+---------------+-------+
| id | phone_id | tarrif_id | affilicate_id | point |
+----+----------+-----------+---------------+-------+
|  1 |        1 |         1 |             1 |   3.3 |
|  2 |        1 |         2 |             2 |   7.3 |
+----+----------+-----------+---------------+-------+
share|improve this question
add comment

1 Answer 1

Things I would do to make it better:

Use some sort of KVP table for the features, and maybe standardize the description part into columns, so you can later query for all values where minutes > 360 or something.

I'm not sure what your intent is, and can offer more specific feedback with more detail. It certainly seems to do what you want right now. That's usually sufficient grounds for recordkeeping.

share|improve this answer
    
Description part into columns is a good idea and I will look into that. My question was is the relationship design between phone, tarrif, network and saleType is ok? I assume you understood from my tables design? Basically operator can select a retailer which then select Mobile Network.. then select what type of sale (eg: consumer upgrade) and then select phone with related related tariffs. Depending what tarrifs and/or phone they have selected - operator gets number of points. –  I'll-Be-Back Sep 17 '12 at 18:52
    
My point was we need a lot more information about your domain model to know if this is a good design. It very well could be a good design. The best I can offer is to make the computer mimic the office-paperwork. –  jcolebrand Sep 17 '12 at 21:29
add comment

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.