MySQL Big tables management?

I need to make this implementation:

I'm using Php and MySQL. As you can see the prices can be dynamically built. In order to manage this requirement I have made the following schema:

`tbl_states` (id, state)
`tbl_prices` (id, price)
`tbl_years`  (id, year)
`tbl_states_prices` (id, state_id, customer_id, year_id, price_id, value)

The values for each price are going to be stored in tbl_states_prices. The values depend on the State, the Customer and the Year. My concern i开发者_StackOverflow社区s on the size of this table as the maximum number of years is 20, prices are about 6 and the average number of customers is 300.

I don't know what are the consequences of using this schema. How should I manage this table? Split into small tables per customer? Views? My lack of experience with Databases does not help me. Suggestions will be appreciated.

You gave few information, so again I have to guess a scenario on my own, this is a possible one.

We keep info for retailers. Every retailer has many customers, and a customer could be customer of many retailers so this a many to many relationship, which requires an other table to help.

For every pair of cutomer and retailer we keep unique charges (forecasting). One charge belongs a one customer only.

Year1, year2 fields no, year must be a single field not many fields. And because this is a field with multiple occurences (similar to the telephone case) good practice requires an other table to help us. So there is not such thing like dynamic fields, fields are static like stone, a foundamental rule! In addition if we are talking about really huge data (which I do not think is the case, we could break the charges table in parts related to a specific year for example, but this requires more work).
A very important thing.. you might have a specific way on your mind how you will present this data, year 1, year 2 etc horizontally, vertically.. however you never let this ideas interfere on your relational diagram, visual display (may change any time) and er design must be kept separate.

As for states there is a small trick to keep the field small, which means faster job and omitting the state table.It requires a simple php function that when given a state e.g California it stores a tinyint instead of the varchar California. Or you can keep a state table with its tinyint id passed as foreign key to charges table. Chose whichever you like.

/Entity is a generalization for Retailer and Customer. A generalization is need to handle similar fields for example both Retailer and Customer have email, address, etc. On the other hand customer and Retailer are a specification of the entity we need it to handle fields that are different for example we don't care if a retialer is married!/

Below is a guiding example only.

drop table if exists `Entity_Phone`;
drop table if exists `Retailer_Customer`;
drop table if exists `Charge`;
drop table if exists `Retailer`;
drop table if exists `Customer`;

drop table if exists `Entity`;
  CREATE TABLE `Entity` (
  `entity_id` INT UNSIGNED NOT NULL   AUTO_INCREMENT,
  /*example code 0 is for retailer and code 1 is for customer*/
  `entity_code` tinyint not null,
  `entity_other_field` VARCHAR(30) NOT NULL,
  PRIMARY KEY  (`entity_id`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
/*You don't have to use utf8 if not needed*/

/*Phone number, instead of only phone you can store more info
like multiple emails etc, just change the table name to  make sense,
the datatype and in the php function that associates codes add 
as many codes needed*/
CREATE TABLE `Entity_Phone` (
  `entity_id` INT UNSIGNED NOT NULL  ,
  `phone` BIGINT UNSIGNED NOT NULL  ,
  /*code 1 is for fix phone, 2 is for mobile phone and 3 for fax*/
  `identification_code`  TINYINT UNSIGNED DEFAULT '1' NOT NULL,
  PRIMARY KEY  (`entity_id`),
  CONSTRAINT `fk1EntData` FOREIGN KEY (`entity_id`)
        REFERENCES `Entity` (`entity_id`)
        ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=COMPACT;


  CREATE TABLE `Retailer` ( 
  `retailer_fname` VARCHAR(30) NOT NULL,
  `retailer_lname` VARCHAR(30) NOT NULL,
  /*pkey directly shared from entity table, just with a different name*/
  `retailer_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY  (`retailer_id`),
  CONSTRAINT `fk1RetEnt` FOREIGN KEY (`retailer_id`)
        REFERENCES `Entity` (`entity_id`)
        ON DELETE CASCADE
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

  CREATE TABLE `Customer` ( 
  `customer_fname` VARCHAR(30) NOT NULL,
  `children_number` tinyint not null,
   `customer_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY  (`customer_id`),
   CONSTRAINT `fk1CustData` FOREIGN KEY (`customer_id`)
        REFERENCES `Entity` (`entity_id`)
        ON DELETE CASCADE
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;



CREATE TABLE `Retailer_Customer` ( 
  `customer_id` INT UNSIGNED NOT NULL  ,
  `retailer_id` INT UNSIGNED NOT NULL  ,   
  PRIMARY KEY  (`customer_id`,`retailer_id`),
  CONSTRAINT `fk1RetCust` FOREIGN KEY (`customer_id`)
        REFERENCES `Entity` (`entity_id`)
        ON DELETE CASCADE, 
 CONSTRAINT `fk2RetCust` FOREIGN KEY (`retailer_id`)
        REFERENCES `Entity` (`entity_id`)
        ON DELETE CASCADE

) ENGINE=InnoDB ROW_FORMAT=COMPACT;




/*
if you want to keep the state table... for example you might need to store 
information for states so you need this table to keep those fields
CREATE TABLE `State` (
  `state_id` tinyINT UNSIGNED NOT NULL  ,
  `state_name` varchar(50) not null ,  
  PRIMARY KEY  (`state_id`),
  unique(`state_name`) 
) ENGINE=InnoDB ROW_FORMAT=COMPACT;
*/
CREATE TABLE `Charge` ( 
  `retailer_id` INT UNSIGNED NOT NULL  ,
   `customer_id` INT UNSIGNED NOT NULL  ,
  `state_code` TINYINT UNSIGNED NOT NULL  ,
  /*state  could be stored here directly as 
  varchar however this way it asks less space,
  is faster and allows no orthographical erros 
 on insertion */
  /*`state_id` tinyint UNSIGNED NOT NULL  , if you want the state table*/
    `charge_date_time`  DATETIME  NOT NULL,
  index(`customer_id`),
  PRIMARY KEY  (`retailer_id`,`customer_id`,`charge_date_time`),
  CONSTRAINT `fk1Charge` FOREIGN KEY (`retailer_id`)
        REFERENCES `Retailer` (`retailer_id`)
        ON DELETE CASCADE,
  CONSTRAINT `fk2Charge` FOREIGN KEY (`customer_id`)
        REFERENCES `Customer` (`customer_id`)
        ON DELETE CASCADE 
 /* if you want the state table
 ,CONSTRAINT `fk2pr` FOREIGN KEY (`state_id`)
        REFERENCES `State` (`state_id`)
        ON DELETE CASCADE
*/
) ENGINE=InnoDB ROW_FORMAT=COMPACT;



/*This is how you insert a Retailer*/  
insert into `Entity` (`entity_code`, `entity_other_field`)
  values ('0','test');
insert into `Retailer` (`retailer_fname`, `retailer_lname`,
  `retailer_id`) values ('John', 'Smith',(SELECT LAST_INSERT_ID()));
insert into `Entity_Phone` (`entity_id`, `phone`,`identification_code`) values 
((SELECT LAST_INSERT_ID()), 123222,3);
/****************************************************/


/*This is how you insert a Customer*/ 
insert into `Entity` (`entity_code`, `entity_other_field`)
  values ('1','test');
insert into `Customer` (`customer_fname`, `children_number`,
  `customer_id`) values ('Jimm', 3,(SELECT LAST_INSERT_ID()));
insert into `Entity_Phone` (`entity_id`, `phone`,`identification_code`) values 
((SELECT LAST_INSERT_ID()), 43543,3);
/****************************************************/




/*This is how you insert a charge*/
insert into `Charge` (`retailer_id`, `customer_id`,`state_code`,
`charge_date_time`)
  values ((select `retailer_id` from `Retailer` where `retailer_fname`='John'),
  (select   `customer_id` from `Customer` where `customer_fname`='Jimm'),34,(now()));

  /*This is how you retrieve a charge*/
  select * from `Charge` where year(`charge_date_time`) ='2011'

1. Step 1 = Requirement Analysis (have a analytic discussion with people having the knowledge) You identify entities (simply said, nouns are usually entities: person, customer, supplier etc and verbs are usually relations: works, buys, supplies etc) So is the price an entity(doubt on it), is the retailer (I bet on this) etc. The retailer could be an entity cause it can exists indepndently while a phone number cannot exists independently, a phone number is something meaningless unless related to a person! A customer can exists on its own, it makes sense!

2. An other thing (among many many others) is to ask your self, what I am going to ask the database,

   • will I ask for price related to state only, or to state and year only etc

I have to stop theory here cause it is getting too big, and because theory is not much of help I will make some very practical notices:

Disclaimer

Keep in mind that I am not aware of the requirements analysis, so I might be tottaly wrong somewhere

• Having a main table that represents an entity (such tables are those without foreign keys) about prices (the way YOU define it) might be a bad practice it might harm your work somewhere. This means inconsistency, having a bad design, a non effective one etc is tolerable, inconsistency IS NOT. Prices and values... I am pretty sure that there is not such thing like values, only prices! So the design saying price 1, price 2 etc I believe is not the way to go if we are talking for database tables.

  • Having a table for years, no way! Year is a field, is an attribute of an entity, not an entity!

  • States, what do you mean like New York etc, some times it is a field.

• A well designed table has got a primary key a simple one (e.g. customer id) or a composite one (e.g student id and course id). In tbl_states_prices the first field contains an id, I am pretty this id is not only useless but also misleading and maybe harmful. maybe you did this cause some of the foreign key could be empty, which means bad design.

A fantastic system requirements and design could be this:

1. We keep info about retailers, so retailer is an entity with fields like name, location, website, state (New York), phone (usually stored in a second table cause is a value with multiple occurences) etc. From your screenshoots it follows that you call the retailer a customer.

tbl_retailer (retail_id, name, surname etc)

1. Then you have a depended (with foreign keys) table with a composite primary key. Now you have to decide how detailed will the date be. You cannot talk about year if you have many prices for a year, you have to talk about month, weeks, or even hours, all depends on how often the prices change.

tbl_prices (retail_id,state,date,time)

all fields make a composite primary key! There is no chance that you will have inconsistent records this way. In your design I could enter the same price, for the same customer, the same state a million times and the only difference would be a meaningless id (the first field of tbl_states_prices). It is like inserting a million times 1. John Smith Father Married etc 2. John Smith Father Married etc .... 1000000. John Smith Father Married etc

How consistent, effective etc is this?!

I deleted a small part of this answear, it is covered and improved in a new answear.

If you want an advise, i recommend you to use MyISAM engine on the tables because of the high speed of selects. I use InnoDB on tables with +1 Million rows because i need to use foreign keys constraints, so thats nothing for mysql.