The program basically handles referrals and group balances. I.e. when someone makes an order, the company will give money to a community group, and people who refer someone else get money off a purchase.
Basically I was wondering if my database design seems logical.
I also have to store the order.
My database design:
CREATE TABLE `meals`.`customers` ( `mint_id` VARCHAR( 15 ) NOT NULL , `first_name` TINYTEXT NOT NULL , `last_name` TINYTEXT NOT NULL , `address` VARCHAR( 255 ) NOT NULL , `city` TINYTEXT NOT NULL , `postal_code` VARCHAR( 10 ) NOT NULL , `home_phone` VARCHAR( 15 ) NOT NULL , `cell_phone` VARCHAR( 15 ) NULL DEFAULT NULL , `email` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `mint_id` ) ) ENGINE = MYISAM Referral Table: CREATE TABLE `meals`.`referrals` ( `ref_id` TINYTEXT NOT NULL , `earnings` DOUBLE NOT NULL , `referred_by` TINYTEXT NOT NULL ) ENGINE = InnoDB Order table: CREATE TABLE `meals`.`order` ( `ord_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `order_size` ENUM( 'full', 'split' ) NOT NULL , `order_qty` ENUM( '12', '9', '6', '3' ) NOT NULL , `order` TEXT NOT NULL ) ENGINE = InnoDB Group balance table: CREATE TABLE `meals`.`groups` ( `grp_name` VARCHAR( 255 ) NOT NULL , `grp_earnings` DOUBLE NOT NULL ) ENGINE = InnoDB
I don't think I get this, there has to be a way to link a customer to an order and a customer to the referrals and a few other links. Can anybody help with these links, and is this design logical at all? I got the customer table good but I'm lost with the logic of the others.