База данных MySQL homeowner

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

Пожалуйста, укажите все неправильные методы кодирования.

create table home_owner
(
    owner_id        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name      VARCHAR(10),
    last_name       VARCHAR(20)
);

create table email
(
    email_id        SMALLINT UNSIGNED PRIMARY KEY,
    email_address   VARCHAR(40)
);

create table owner_email
(
    owner_id        SMALLINT UNSIGNED,
    email_id        SMALLINT UNSIGNED,
    PRIMARY KEY emails_PK(owner_id, email_id),
    CONSTRAINT owner_email_FK FOREIGN KEY(owner_id) REFERENCES    home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT email_owner_FK FOREIGN KEY(email_id) REFERENCES email(email_id) ON DELETE CASCADE
);

create table phone_number
(
    number_id       SMALLINT UNSIGNED PRIMARY KEY,
    phone_number    VARCHAR(10)
);

create table owner_number
(
    owner_id        SMALLINT UNSIGNED,
    number_id       SMALLINT UNSIGNED,
    PRIMARY KEY numbers_PK(owner_id, number_id),
    CONSTRAINT owner_number_FK FOREIGN KEY(owner_id) REFERENCES         home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT number_owner_FK FOREIGN KEY(number_id) REFERENCES phone_number(number_id) ON DELETE CASCADE
);

create table seasonal_address
(
    seasonal_id     SMALLINT UNSIGNED PRIMARY KEY,
    address         VARCHAR(50),
    months          VARCHAR(27)
);

create table contact_info
(
    owner_id        SMALLINT UNSIGNED,
    seasonal_id     SMALLINT UNSIGNED,
    PRIMARY KEY contact_PK(owner_id, seasonal_id),
    CONSTRAINT owner_info_FK FOREIGN KEY(owner_id) REFERENCES home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT seasonal_info_FK FOREIGN KEY(seasonal_id) REFERENCES seasonal_address(seasonal_id) ON DELETE CASCADE
);

create table property
(
    property_id     SMALLINT UNSIGNED PRIMARY KEY,
    address         VARCHAR(50),
    lot_number      SMALLINT UNSIGNED
);

create table owner_property
(
    owner_id        SMALLINT UNSIGNED,
    property_id     SMALLINT UNSIGNED,
    PRIMARY KEY properties_PK(owner_id, property_id),
    CONSTRAINT owner_property_FK FOREIGN KEY(owner_id) REFERENCES home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT property_owner_FK FOREIGN KEY(property_id) REFERENCES property(property_id) ON DELETE CASCADE
);

insert into home_owner (first_name, last_name) values
("Angel", "Flop"),
("Bob", "Hoe"),
("Sue", "Hoe");

insert into email values
(1, "[email protected]"),
(2, "[email protected]"),
(3, "[email protected]");

insert into property values
(1, "123 Rainey", 123),
(2, "234 Bob", 1298),
(3, "697 Kolp", 782);

insert into seasonal_address values
(1, "7667 Noob", "1,2,3"),
(2, "2383 Fob", "4,5,6,7,8,9,10,11,12"),
(3, "7823 Flower", "1,2,3,4,5,6,7,8,9,10,11,12");

insert into phone_number values
(1, "5203601083"),
(2, "8039023093"),
(3, "2387784334"),
(4, "2377823782");

insert into owner_number values
(1, 1),
(1, 2),
(2, 3),
(3, 4);

insert into contact_info values
(1, 1),
(1, 2),
(2, 3),
(3, 3);

insert into owner_property values
(1, 1),
(1, 2),
(2, 3),
(3, 3);

insert into owner_email values
(1, 1),
(2, 2),
(3, 3);

select af.property_id 
from property af
inner join owner_property op
on af.property_id = op.property_id
where op.owner_id = 1; //find all properties of owner_id = 1

select a.*
from home_owner a
inner join owner_property op
on a.owner_id = op.owner_id
where op.property_id = 3; //find all owners of property_id = 3

Определения таблиц здесь .

11 голосов | спросил Angel Bates 18 FriEurope/Moscow2015-12-18T01:51:19+03:00Europe/Moscow12bEurope/MoscowFri, 18 Dec 2015 01:51:19 +0300 2015, 01:51:19

1 ответ


8

Для определения таблиц просто используйте INT для ключей. Вероятно, вы ничего не спасаете, используя SMALLINT s. Изменения схемы, как правило, являются болью в крупных проектах, поэтому вы можете также попытаться избежать любой проблемы.

CREATE UNIQUE INDEX index_PK ON owner_property(property_id, owner_id); является избыточным, поскольку в таблице уже есть PRIMARY KEY properties_PK(owner_id, property_id)

Ограничения внешнего ключа выглядят хорошо.

Имея отдельные таблицы только для email и phone_number, вероятно, перебор. Адреса электронной почты и номера телефонов могут быть просто атрибутами owner_email и owner_number, соответственно. Я бы поставил ограничения NOT NULL для обоих этих атрибутов. Поле phone_number должно быть длиннее, чем VARCHAR(10). (Обратите внимание, что MySQL имеет тенденцию молча отбрасывать часть строки за пределы длины.)

Аналогично, VARCHAR(50) для адреса свойства, вероятно, слишком скупо.


Для вашего первого запроса ...

select af.property_id 
from property af
inner join owner_property op
on af.property_id = op.property_id
where op.owner_id = 1; //find all properties of owner_id = 1

... вам вообще не нужно ничего присоединяться.

select property_id 
    from owner_property op
    where owner_id = 1;

Для вашего второго запроса ...

select a.*
from home_owner a
inner join owner_property op
on a.owner_id = op.owner_id
where op.property_id = 3; //find all owners of property_id = 3
  • Используйте лучший отступ. SELECT имеет предложение FROM и a ---- +: = 17 =: + ----. WHERE имеет FROM. INNER JOIN имеет INNER JOIN.
  • Используйте псевдоним таблицы, который имеет смысл (или вообще не использует псевдоним).
ON
ответил 200_success 18 FriEurope/Moscow2015-12-18T02:32:58+03:00Europe/Moscow12bEurope/MoscowFri, 18 Dec 2015 02:32:58 +0300 2015, 02:32:58

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132