Схема базы данных для проекта управления сервисного заказа


Я студент и я создал эту схему базы данных MySQL для управления заказами услуг Академический проект. Я хочу знать, если мне нужно, чтобы улучшить его и, если да, то как я могу это сделать.

/*
 * Table 'brands'
 */
CREATE TABLE `brands` (
    `brand_id` INTEGER NOT NULL,
    `equipment_id` INTEGER NOT NULL,
    `brand` VARCHAR(45) NOT NULL,
    CONSTRAINT `pk_brand_id` PRIMARY KEY (`brand_id`),
    CONSTRAINT `fk_equipment_id` FOREIGN KEY (`equipment_id`) REFERENCES `equipments`
);

/*
 * Table 'cities'
 */
CREATE TABLE `cities` (
    `city_id` INTEGER NOT NULL,
    `state_id` INTEGER NOT NULL,
    `city` VARCHAR(50) NOT NULL,
    CONSTRAINT `pk_city_id` PRIMARY KEY (`city_id`),
    CONSTRAINT `fk_state_id` FOREIGN KEY (`state_id`) REFERENCES `states`
);

/*
 * Table 'customers'
 */
CREATE TABLE `customers` (
    `customer_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    CONSTRAINT `pk_customer_id` PRIMARY KEY (`customer_id`),
    CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`
);

/*
 * Table 'employees'
 */
CREATE TABLE `employees` (
    `employee_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `is_admin` INTEGER NOT NULL,
    CONSTRAINT `pk_employee_id` PRIMARY KEY (`employee_id`),
    CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`,
    CONSTRAINT `ck_is_admin` CHECK (`is_admin` IN (0, 1)) -- unsupported by MySQL
);

/*
 * Table 'equipments'
 */
CREATE TABLE `equipments` (
    `equipment_id` INTEGER NOT NULL,
    `equipment` VARCHAR(45) NOT NULL,
    CONSTRAINT `pk_equipment_id` PRIMARY KEY (`equipment_id`)
);

/*
 * Table 'legal_users'
 */
CREATE TABLE `legal_users` (
    `user_id` INTEGER NOT NULL,
    `cnpj` CHAR(14) NOT NULL,
    `company_name` VARCHAR(150) NOT NULL,
    `trade_name` VARCHAR(100) NULL,
    `contact_name` VARCHAR(50) NOT NULL,
    `mobile_phone` CHAR(10) NULL,
    `work_phone` CHAR(10) NULL,
    CONSTRAINT `pk_user_id` PRIMARY KEY (`user_id`),
    CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`,
    CONSTRAINT `uc_cnpj` UNIQUE (cnpj)
);

/*
 * Table 'natural_users'
 */
CREATE TABLE `natural_users` (
    `user_id` INTEGER NOT NULL,
    `cpf` CHAR(11) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `nickname` VARCHAR(30) NULL,
    `sex` CHAR(1) NOT NULL,
    `birth_date` DATE NOT NULL,
    `home_phone` CHAR(10) NULL,
    `mobile_phone` CHAR(10) NULL,
    `work_phone` CHAR(10) NULL, -- ignored for employees
    CONSTRAINT `pk_user_id` PRIMARY KEY (`user_id`),
    CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`,
    CONSTRAINT `uc_cpf` UNIQUE (`cpf`),
    CONSTRAINT `ck_sex` CHECK (`sex` IN ('M', 'F')) -- unsupported by MySQL
);

/*
 * Table 'service_orders'
 */
CREATE TABLE `service_orders` (
    `order_id` INTEGER NOT NULL,
    `employee_id` INTEGER NOT NULL,
    `customer_id` INTEGER NOT NULL,
    `opening_date` TIMESTAMP NOT NULL,
    `status` INTEGER NOT NULL,
    `equipment_id` INTEGER NOT NULL,
    `brand_id` INTEGER NOT NULL,
    `model` VARCHAR(20) NOT NULL,
    `serial_number` VARCHAR(20) NULL,
    `under_warranty` INTEGER NOT NULL,
    `defect` TEXT NOT NULL,
    `diagnosis` TEXT NULL,
    `solution` TEXT NULL,
    `maintenance_cost` DECIMAL(6,2) NULL,
    `closing_date` TIMESTAMP NULL,
    `remarks` TEXT NULL,
    CONSTRAINT `pk_order_id` PRIMARY KEY (`order_id`),
    CONSTRAINT `fk_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `employees`,
    CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers`,
    CONSTRAINT `ck_status` CHECK (`status` IN (0, 5)), -- unsupported by MySQL
    CONSTRAINT `fk_equipment_id` FOREIGN KEY (`equipment_id`) REFERENCES `equipments`,
    CONSTRAINT `fk_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `brands`,
    CONSTRAINT `ck_under_warranty` CHECK (`under_warranty` IN (0, 1)) -- unsupported by MySQL
);

/*
 * Table 'states'
 */
CREATE TABLE `states` (
    `state_id` INTEGER NOT NULL,
    `state` VARCHAR(20) NOT NULL,
    `code` CHAR(2) NOT NULL,
    CONSTRAINT `pk_state_id` PRIMARY KEY (`state_id`),
    CONSTRAINT `uc_state` UNIQUE (`state`),
    CONSTRAINT `uc_code` UNIQUE (code)
);

/*
 * Table 'users'
 */
CREATE TABLE `users` (
    `user_id` INTEGER NOT NULL,
    `person_type` CHAR(1) NOT NULL,
    `postal_code` CHAR(8) NOT NULL,
    `address` VARCHAR(60) NOT NULL,
    `number` INTEGER NULL,
    `complement` VARCHAR(50) NULL,
    `neighborhood` VARCHAR(30) NOT NULL,
    `city_id` INTEGER NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `password` CHAR(40) NOT NULL,
    `remarks` TEXT NULL,
    `is_active` INTEGER NOT NULL DEFAULT 1,
    CONSTRAINT `pk_user_id` PRIMARY KEY (`user_id`),
    CONSTRAINT `ck_person_type` CHECK (`person_type` IN ('F', 'J')), -- unsupported by MySQL
    CONSTRAINT `fk_city_id` FOREIGN KEY (`city_id`) REFERENCES `cities`,
    CONSTRAINT `uc_email` UNIQUE (`email`),
    CONSTRAINT `ck_is_active` CHECK (`is_active` IN (0, 1)) -- unsupported by MySQL
);


1791
5
задан 28 октября 2011 в 05:10 Источник Поделиться
Комментарии
3 ответа

Он выглядит прекрасно. Две вещи, чтобы рассмотреть:

1, отдельных таблиц по телефонам:

phone_number
id
user_id
type (home/work/mobile/etc)
number

Его может не стоит, зависит от данных.

2, может быть, вы хотите сохранить некоторые данные аудита о ваших данных. (Подсказки: журнал таблица в Oracle, гибернации Энверов)

+1: тип char(40) для пароля - это Ша-1? Если вы использовать SHA-256/ша-512, он будет слишком коротким.

4
ответ дан 28 октября 2011 в 11:10 Источник Поделиться

Вы можете установить целых чисел, которые выступают в качестве идентификатора к типу 'unsigned'. Это действительно не намного улучшится, то это только дает вам более широкий диапазон целочисленных значений.

1
ответ дан 28 октября 2011 в 06:10 Источник Поделиться

Для государства, в этом больше смысла. Отрегулируйте внешние ключи, чтобы соответствовать.

CREATE TABLE `states` (
`code` CHAR(2) NOT NULL,
`state` VARCHAR(20) NOT NULL,
CONSTRAINT `pk_states` PRIMARY KEY (`code`),
CONSTRAINT `uc_state` UNIQUE (`state`)
);

Используя идентификационный номер суррогатный ключ имеет смысл только если


  • что вы моделируете не носит свою идентичность вместе с ним, или

  • его личность нереально долго, как varchar(250).

Оба государства код и название государства полностью идентифицировать. (В рамках одной страны.) С помощью кода, а не целое число, экономит пространство и, как правило, исключает соединения.

-2
ответ дан 10 ноября 2012 в 12:11 Источник Поделиться