Existem ferramentas de benchmarking do MySQL? [fechado]

Question

newuser

Asked: 2012-03-22 22:33:10 +0800 CST2012-03-22 22:33:10 +0800 CST 2012-03-22 22:33:10 +0800 CST

Usando chaves estrangeiras no banco de dados

772

Estou fazendo uma pequena aplicação para Nota Fiscal. Em que as tabelas ficarão assim

=== invoice ===
id (pk)
customer_id (fk)
invoice_title
invoice_issue_date
due_date
description
created_by
updated_by
created_at
updated_at

=== invoice_items ===
id (pk)
invoice_id (fk)
customer_id (fk)
product_name 
unit_cost 
quantity 
apply_tax1 
apply_tax2 
discount 
description 
created_by 
updated_by 
created_at 
updated_at 

=== customers ===
id (pk)
business_email 
business_name 
customer_name 
business_address 
town/city 
state/province 
postalzip_code 
country 
phone 
mob 
fax 
created_by 
updated_by 
created_at 
updated_at 

=== estimates ===
id (pk)
address 
estimate_title 
estimate_no 
purchase_order_no 
estimate_date 
description 
created_by 
updated_by 
created_at 
updated_at 

=== estimate_items ===
id (pk)
estimate_id (fk)
customer_id (fk)
product/service 
unit_cost 
quantity 
apply_tax1 
apply_tax2 
discount 
description 
created_by 
updated_by 
created_at
updated_at 

=== projects ===
id (pk)
customer_id (fk)
project_name 
description 
purchase_order_no 
budget float 
billing_method 
flat_amount 
created_by 
updated_by 
created_at 
updated_at 

=== tasks ====
id (pk)
task_name 
description 
billable 
task_rate 
start_date 
end_date 
estimated_hours 
billing_method 
flat_amount
created_by 
updated_by 
created_at 
updated_at

Para fazer tudo isso no banco de dados MySQL, fiz esta consulta SQL

-- phpMyAdmin SQL Dump
-- version 3.3.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `nt_invoice`
--

-- --------------------------------------------------------

--
-- Table structure for table `nt_customers`
--

CREATE TABLE IF NOT EXISTS `nt_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `business_email` varchar(80) NOT NULL DEFAULT '',
  `business_name` varchar(80) NOT NULL DEFAULT '',
  `customer_name` varchar(80) NOT NULL DEFAULT '',
  `business_address` text,
  `town/city` varchar(80) NOT NULL DEFAULT '',
  `state/province` varchar(80) NOT NULL DEFAULT '',
  `postalzip_code` varchar(25) NOT NULL DEFAULT '',
  `country` varchar(80) NOT NULL DEFAULT '',
  `phone` varchar(25) DEFAULT NULL,
  `mob` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_customers`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_estimates`
--

CREATE TABLE IF NOT EXISTS `nt_estimates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `address` text NOT NULL,
  `estimate_title` varchar(80) NOT NULL,
  `estimate_no` varchar(25) NOT NULL,
  `purchase_order_no` varchar(25) NOT NULL,
  `estimate_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_estimates`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_estimate_items`
--

CREATE TABLE IF NOT EXISTS `nt_estimate_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `estimate_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product/service` varchar(45) NOT NULL,
  `unit_cost` varchar(45) NOT NULL,
  `quantity` varchar(45) NOT NULL,
  `apply_tax1` varchar(25) NOT NULL,
  `apply_tax2` varchar(25) NOT NULL,
  `discount` varchar(25) NOT NULL,
  `description` text NOT NULL,
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `estimate_id` (`estimate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_estimate_items`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_invoices`
--

CREATE TABLE IF NOT EXISTS `nt_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `invoice_title` varchar(80) NOT NULL,
  `invoice_issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_invoices`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_invoice_items`
--

CREATE TABLE IF NOT EXISTS `nt_invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product_name` varchar(45) NOT NULL,
  `unit_cost` float DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `apply_tax1` float NOT NULL,
  `apply_tax2` float NOT NULL,
  `discount` float NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_invoice_items`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_projects`
--

CREATE TABLE IF NOT EXISTS `nt_projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `project_name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  `purchase_order_no` varchar(80) NOT NULL,
  `budget` float NOT NULL DEFAULT '0',
  `billing_method` varchar(80) NOT NULL,
  `flat_amount` float NOT NULL DEFAULT '0',
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_projects`
--


-- --------------------------------------------------------

--
-- Table structure for table `nt_tasks`
--

CREATE TABLE IF NOT EXISTS `nt_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `task_name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  `billable` varchar(80) NOT NULL,
  `task_rate` float NOT NULL DEFAULT '0',
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `estimated_hours` varchar(25) NOT NULL,
  `billing_method` varchar(80) NOT NULL,
  `flat_amount` float NOT NULL DEFAULT '0',
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_tasks`
--

Então, eu quero saber se isso está correto?Tenho a dúvida ForeignKeys(fk), eles estão definidos corretamente nesta consulta?por favor me ajude a fazer isso, pois sou novato no MySQL.Qualquer ajuda e sugestões são altamente apreciáveis

1 respostas

Voted

ypercubeᵀᴹ · Answer 1 · 2012-03-22T23:35:18+08:00

Não, você não definiu nenhuma restrição de chave estrangeira.

A página de documentação do MySQL sobre FOREIGN KEYrestrições tem mais detalhes e opções possíveis.

Aqui está um exemplo para a invoices -> customerschave estrangeira:

CREATE TABLE IF NOT EXISTS `nt_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `invoice_title` varchar(80) NOT NULL,
  `invoice_issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY customer_id_index (customer_id),        --- index to be used by the FK

  CONSTRAINT customer_invoice_fk              --- the FK constraint's name
    FOREIGN KEY (customer_id)
      REFERENCES nt_customers(id)
      ON UPDATE CASCADE                       --- or other action
      ON DELETE RESTRICT                      --- here, too  

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Usando chaves estrangeiras no banco de dados

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Usando chaves estrangeiras no banco de dados

1 respostas

relate perguntas