5 Replies - 952 Views - Last Post: 12 April 2009 - 03:34 PM

#1 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Help with Database Design

Posted 09 April 2009 - 05:49 PM

I'm starting a basic project using a MySQL database.

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Help with Database Design

#2 sam.adams61  Icon User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 283
  • Joined: 14-July 08

Re: Help with Database Design

Posted 09 April 2009 - 06:14 PM

View Postchili5, on 9 Apr, 2009 - 11:49 PM, said:

I'm starting a basic project using a MySQL database.

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.

While I have no experience with MySql, I can't help wondering why you have no Primary Key set on the Referrals table? And why can't you use the Primary Key on the Customer's table to link to the Referrals, ie One customer, many referrals? The same would go for the Orders table with the Primary Key from customers acting as a Foreign Key.
Hope you get it sorted.
Was This Post Helpful? 1
  • +
  • -

#3 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Re: Help with Database Design

Posted 10 April 2009 - 04:15 AM

Ok thanks I think I got that all sorted out.

Now can you help me with one small question?

How would be best to store the order? Should I just build a long string that represents the order? Or have a products table, and have many orders to many products?
Was This Post Helpful? 0
  • +
  • -

#4 sam.adams61  Icon User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 283
  • Joined: 14-July 08

Re: Help with Database Design

Posted 10 April 2009 - 02:29 PM

How would be best to store the order? Should I just build a long string that represents the order? Or have a products table, and have many orders to many products?
[/quote]
What you would have is not many orders to many products, but one order to many products, ie each order can consist of one or more products. Its like going to the store, you place one order and get either one or more products.
My advice, try and keep it as simple as you possibly can.
All the best, and I trust that all will go well with your project...if you're stuck, come back to DIC, there's always someone on hand to help.
God bless.
Was This Post Helpful? 0
  • +
  • -

#5 chili5  Icon User is offline

  • D.I.C Lover

Reputation: 20
  • View blog
  • Posts: 1,144
  • Joined: 28-December 07

Re: Help with Database Design

Posted 11 April 2009 - 04:37 AM

Thanks man you have been a great help. I got all the database tables created in a way I am happy with.

You are so helpful. Thanks so much!!!!!! :)

Posted Image
Was This Post Helpful? 0
  • +
  • -

#6 jemagee  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 38
  • Joined: 18-October 08

Re: Help with Database Design

Posted 12 April 2009 - 03:34 PM

When building an 'order' type system - the general practice is to have an order header and an orderline table - the orderline table is an intermediate table that has a many to many relationship with your orderheader AND your Products wither orderID and productID serving as a foreign key.

IDEAL database structre indicates you'd have a concatenated key of OrderID/ProductID on this table - but in praciticality you probably won't because people want to be able to put the same item/product twice on the same order.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1