L2dBFpMR

1MySQL Big tables management?
2`tbl_states` (id, state)
3`tbl_prices` (id, price)
4`tbl_years`  (id, year)
5`tbl_states_prices` (id, state_id, customer_id, year_id, price_id, value)
6	
7drop table if exists `Entity_Phone`;
8drop table if exists `Retailer_Customer`;
9drop table if exists `Charge`;
10drop table if exists `Retailer`;
11drop table if exists `Customer`;
12
13drop table if exists `Entity`;
14  CREATE TABLE `Entity` (
15  `entity_id` INT UNSIGNED NOT NULL   AUTO_INCREMENT,
16  /*example code 0 is for retailer and code 1 is for customer*/
17  `entity_code` tinyint not null,
18  `entity_other_field` VARCHAR(30) NOT NULL,
19  PRIMARY KEY  (`entity_id`)
20) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
21/*You don't have to use utf8 if not needed*/
22
23/*Phone number, instead of only phone you can store more info
24like multiple emails etc, just change the table name to  make sense,
25the datatype and in the php function that associates codes add 
26as many codes needed*/
27CREATE TABLE `Entity_Phone` (
28  `entity_id` INT UNSIGNED NOT NULL  ,
29  `phone` BIGINT UNSIGNED NOT NULL  ,
30  /*code 1 is for fix phone, 2 is for mobile phone and 3 for fax*/
31  `identification_code`  TINYINT UNSIGNED DEFAULT '1' NOT NULL,
32  PRIMARY KEY  (`entity_id`),
33  CONSTRAINT `fk1EntData` FOREIGN KEY (`entity_id`)
34        REFERENCES `Entity` (`entity_id`)
35        ON DELETE CASCADE
36) ENGINE=InnoDB ROW_FORMAT=COMPACT;
37
38
39  CREATE TABLE `Retailer` ( 
40  `retailer_fname` VARCHAR(30) NOT NULL,
41  `retailer_lname` VARCHAR(30) NOT NULL,
42  /*pkey directly shared from entity table, just with a different name*/
43  `retailer_id` INT UNSIGNED NOT NULL ,
44  PRIMARY KEY  (`retailer_id`),
45  CONSTRAINT `fk1RetEnt` FOREIGN KEY (`retailer_id`)
46        REFERENCES `Entity` (`entity_id`)
47        ON DELETE CASCADE
48) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
49
50  CREATE TABLE `Customer` ( 
51  `customer_fname` VARCHAR(30) NOT NULL,
52  `children_number` tinyint not null,
53   `customer_id` INT UNSIGNED NOT NULL ,
54  PRIMARY KEY  (`customer_id`),
55   CONSTRAINT `fk1CustData` FOREIGN KEY (`customer_id`)
56        REFERENCES `Entity` (`entity_id`)
57        ON DELETE CASCADE
58) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
59
60
61
62CREATE TABLE `Retailer_Customer` ( 
63  `customer_id` INT UNSIGNED NOT NULL  ,
64  `retailer_id` INT UNSIGNED NOT NULL  ,   
65  PRIMARY KEY  (`customer_id`,`retailer_id`),
66  CONSTRAINT `fk1RetCust` FOREIGN KEY (`customer_id`)
67        REFERENCES `Entity` (`entity_id`)
68        ON DELETE CASCADE, 
69 CONSTRAINT `fk2RetCust` FOREIGN KEY (`retailer_id`)
70        REFERENCES `Entity` (`entity_id`)
71        ON DELETE CASCADE
72
73) ENGINE=InnoDB ROW_FORMAT=COMPACT;
74
75
76
77
78/*
79if you want to keep the state table... for example you might need to store 
80information for states so you need this table to keep those fields
81CREATE TABLE `State` (
82  `state_id` tinyINT UNSIGNED NOT NULL  ,
83  `state_name` varchar(50) not null ,  
84  PRIMARY KEY  (`state_id`),
85  unique(`state_name`) 
86) ENGINE=InnoDB ROW_FORMAT=COMPACT;
87*/
88CREATE TABLE `Charge` ( 
89  `retailer_id` INT UNSIGNED NOT NULL  ,
90   `customer_id` INT UNSIGNED NOT NULL  ,
91  `state_code` TINYINT UNSIGNED NOT NULL  ,
92  /*state  could be stored here directly as 
93  varchar however this way it asks less space,
94  is faster and allows no orthographical erros 
95 on insertion */
96  /*`state_id` tinyint UNSIGNED NOT NULL  , if you want the state table*/
97    `charge_date_time`  DATETIME  NOT NULL,
98  index(`customer_id`),
99  PRIMARY KEY  (`retailer_id`,`customer_id`,`charge_date_time`),
100  CONSTRAINT `fk1Charge` FOREIGN KEY (`retailer_id`)
101        REFERENCES `Retailer` (`retailer_id`)
102        ON DELETE CASCADE,
103  CONSTRAINT `fk2Charge` FOREIGN KEY (`customer_id`)
104        REFERENCES `Customer` (`customer_id`)
105        ON DELETE CASCADE 
106 /* if you want the state table
107 ,CONSTRAINT `fk2pr` FOREIGN KEY (`state_id`)
108        REFERENCES `State` (`state_id`)
109        ON DELETE CASCADE
110*/
111) ENGINE=InnoDB ROW_FORMAT=COMPACT;
112
113
114
115/*This is how you insert a Retailer*/  
116insert into `Entity` (`entity_code`, `entity_other_field`)
117  values ('0','test');
118insert into `Retailer` (`retailer_fname`, `retailer_lname`,
119  `retailer_id`) values ('John', 'Smith',(SELECT LAST_INSERT_ID()));
120insert into `Entity_Phone` (`entity_id`, `phone`,`identification_code`) values 
121((SELECT LAST_INSERT_ID()), 123222,3);
122/****************************************************/
123
124
125/*This is how you insert a Customer*/ 
126insert into `Entity` (`entity_code`, `entity_other_field`)
127  values ('1','test');
128insert into `Customer` (`customer_fname`, `children_number`,
129  `customer_id`) values ('Jimm', 3,(SELECT LAST_INSERT_ID()));
130insert into `Entity_Phone` (`entity_id`, `phone`,`identification_code`) values 
131((SELECT LAST_INSERT_ID()), 43543,3);
132/****************************************************/
133
134
135
136
137/*This is how you insert a charge*/
138insert into `Charge` (`retailer_id`, `customer_id`,`state_code`,
139`charge_date_time`)
140  values ((select `retailer_id` from `Retailer` where `retailer_fname`='John'),
141  (select   `customer_id` from `Customer` where `customer_fname`='Jimm'),34,(now()));
142
143  /*This is how you retrieve a charge*/
144  select * from `Charge` where year(`charge_date_time`) ='2011'