Правильная связь таблиц

Kitty где схема, которую будем обсуждать?

Цитата Akina @ 24.04.15, 14:24

И много там у ораклистов нерадивых студентов?

Да не студентка она!

Цитата Akina @ 24.04.15, 14:24

Но когда аналогично озвучивается постгресс/файрбёрд/мускул/мсде, сомнения у меня просто-таки чешутся

Тебе же написали - доступ к ЧУЖОЙ базе. А тут надо СВОЮ проектировать.

Добавлено 25.04.15, 09:38

Цитата Павел Калугин @ 25.04.15, 04:36

Kitty где схема, которую будем обсуждать?

+!

По поводу схемы, я просто рисую на бумаге связи, мне надо приложить скан своего листочка?
Я постараюсь выложит свои новые скрипты таблиц, завтра в этой теме. Почему завтра, потому, что задача имеет доп. условие. Об этом условии в первом посте не писала т.к. думала и так все выглядит достаточно сложно. Думала решать на форуме в два этапа. Полностью же задача выглядит так:
Необходимо к системе описанной в первом посте добавить таблицу пользователей. Таблица пользователей состоит из трех видов пользователей:
1. Супервизор.
2. Администратор.
3. Пользователь.
Все они имеют логин, пароль, имя, фамилию и т.д. Логин уникален.
Супервизор может делать все в базе данных. Создавать супервизоров, администраторов, пользователей, назначать администратору его организацию, создавать организации... Короче супервизор может делать все.
Администратор может делать все, только в рамках назначенной ему супервизором организации (например, создавать удалять помещения его конкретной организации, назначать пользователей и те помещения организации которые может видеть этот пользователь).
Пользователь организации не может ничего в базе данных. Он может просматривать только помещения организации которые ему назначил администратор организации. Как пример - у организации 10 помещений и два пользователя. Одному пользователю администратор разрешил просматривать все 10 помещений, а второму пользователю, администратор разрешил просматривать только три конкретных помещения. Просматривать это значить иметь доступ к датчикам в этих помещениях.
Свои таблицы с минимальным кол-вом столбцов и необходимыми связями я постараюсь выложить завтра. Меня сейчас интересуют именно правильные связи на первом этапе.
Думаю, будет много ошибок.

Цитата Kitty @ 25.04.15, 19:12

Меня сейчас интересуют именно правильные связи на первом этапе.

Правильная связь таблиц (сообщение #3594988)

Цитата Kitty @ 25.04.15, 19:12

Необходимо к системе описанной в первом посте добавить таблицу пользователей.

Читайте, умеет ли постгресс ограничивать права на уровне отдельной записи таблицы. А чёта я за ним такого навскидку не припоминаю (разве что косвенно?). Если нет - либо меняйте СУБД, либо требования.

Добавлено 25.04.15, 20:06

Цитата #SI# @ 25.04.15, 09:37

Тебе же написали - доступ к ЧУЖОЙ базе. А тут надо СВОЮ проектировать.

Алё, очкнись! про базу ещё даже речи не шло, а у неё уже СУБД выбрана...

У неё движок выбран.
ЗЫ - флудить не надоело?

Цитата Akina @ 25.04.15, 20:05

Если нет - либо меняйте СУБД, либо требования.

Можно ограничить на уровне интерфейса.

Можно ли обойтись без некоторых таблиц связи, при наличии таких вторичных ключей?

-- таблица организаций
CREATE TABLE organizations
(
  id serial NOT NULL,
  "name" text,
  tel1 text,
  tel2 text,
  person text, -- контактное лицо организации
  prim1 text,
  CONSTRAINT pk_id PRIMARY KEY (id)
)
 
 
-- таблица помещений
-- вторичный ключ для связи помещения-организация
CREATE TABLE rooms
(
  id serial NOT NULL,
  "name" text,
  id_organization integer,
  tel text,
  person text, -- контактное лицо в помещении
  prim1 text,
  CONSTRAINT pk_id_rooms PRIMARY KEY (id),
  CONSTRAINT fk_id_org FOREIGN KEY (id_organization)
      REFERENCES organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
 
 
-- таблица датчиков
-- вторичный ключ для связи датчики-помещения
CREATE TABLE sensors
(
  id serial NOT NULL,
  ser_num integer, -- серийный номер
  id_room integer,
  poverka timestamp without time zone, --дата поверки датчика
  CONSTRAINT pk_id_sensors PRIMARY KEY (id),
  CONSTRAINT fk_to_rooms FOREIGN KEY (id_room)
      REFERENCES rooms (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
 
 
-- таблица пользователей
-- вторичный ключ для организаций
CREATE TABLE users
(
  id serial NOT NULL,
  permission integer,-- доступ: 0-супервизор, 1-администратор, 2-пользователь
  "login" text NOT NULL,-- логин уникален
  "password" text NOT NULL,
  fam text NOT NULL,
  "name" text,
  otchestvo text,
  tel text,
  email text,
  id_organization integer,
  CONSTRAINT pk_user PRIMARY KEY (id),
  CONSTRAINT fk_to_org FOREIGN KEY (id_organization)
      REFERENCES organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT unik_login UNIQUE (login)
)
 
 
-- таблица связи
-- вторичные ключи для установки какие помещения будут закреплены за пользователем
CREATE TABLE users_rooms
(
  id serial NOT NULL,
  id_users integer,
  id_rooms integer,
  CONSTRAINT pk_id_users_rooms PRIMARY KEY (id),
  CONSTRAINT fk_id_rooms FOREIGN KEY (id_rooms)
      REFERENCES rooms (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_id_users FOREIGN KEY (id_users)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

Спасибо.

Вы напрасно проигнорировали то, что ранее сказал Павел Калугин.

Организации, комнаты, датчики и пользователи - это самостоятельные сущности. Да ещё и формально неуничтожимые - с ними выполняются только операции добавления и изменения, но не удаления (нет, удалять-то можно, но смысл?). И отдельно существуют таблицы, устанавливающие связи между сущностями (организация-комната, комната-датчик, комната-пользователь) - вот тут возможен весь пакет операций, и только из этих связующих таблиц будут внешние ключи на таблицы сущностей. А Вы зачем-то опять связываете одни сущности с другими.

Если организация съехала - это означает, что в таблице связей (организация-комната) не останется ни одной ссылки на эту организацию, все комнаты будут либо переписаны на другие организации, либо станут свободными (NULL в поле ИД организации).
Если датчик демонтирован - аналогично в таблице связей (комната-датчик) на этот датчик больше не будет ссылок.

Цитата

Вы напрасно проигнорировали то, что ранее сказал Павел Калугин.

Я не игнорирую. Буду добавлять. Просто хотелось услышать критику по поводу того, что самостоятельно сделала и насколько это неэффективно. Когда понятны ошибки в том, что сделано самостоятельно - легче дальше двигаться.
Спасибо за объяснения.

Цитата Kitty @ 27.04.15, 09:06

хотелось услышать критику по поводу того, что самостоятельно сделала и насколько это неэффективно

Посмотрите на структуру таблицы комнат. В неё Вы ввели поле идентификатора организации.
А теперь подумайте - является ли организация характеристикой комнаты? Или, может, комната существует вне зависимости от какой-то конкретной организации, и даже от существования организаций в этом мире вообще? По-моему, комнате сиренево на организации. Сущность Организация не является атрибутом сущности Комната. Следовательно, полю id_organization в структуре таблицы rooms не место.
Аналогично по другим полям, которые атрибутами некоей сущности на самом деле не являются, но в структуру таблиц для хранения экземпляров этой сущности за каким-то фигом включены.

Цитата Kitty @ 27.04.15, 09:06

Я не игнорирую.

Вы игнорируете. Но это полбеды. Вы НЕ ПОНИМАЕТЕ, что на самом деле игнорируете. А вот это уже беда.

то, что #SI# считает оффтопом

Я в самом начале темы настоятельно рекомендовал Вам почитать теорию. По анализу преметной области - вообще обязательно. А если Вы наткнётесь на неизвестные Вам основы и термины (а Вы обязательно наткнётесь, потому как явно слабо себе представляете, что такое нормальные формы) - то и их надо бы почитать. Ну хоть немного. Иначе так и будете тыркаться, как слепой котёнок. Или пока за Вас всё кто-то не сделает.

Вот так вроде правильно?
И мы везде ставим ON DELETE SET NULL т.к. это в таблицах связи при такой структуре это логичнее всего?

CREATE TABLE organizations
(
  id serial NOT NULL,
  "name" text NOT NULL,
  tel1 text,
  tel2 text,
  person text,
  prim1 text,
  CONSTRAINT pk_id PRIMARY KEY (id)
);
 
 
 
CREATE TABLE rooms
(
  id serial NOT NULL,
  "name" text NOT NULL,
  tel text,
  person text,
  prim1 text,
  CONSTRAINT pk_id_rooms PRIMARY KEY (id)
);
 
 
CREATE TABLE sensors
(
  id serial NOT NULL,
  ser_num integer NOT NULL,
  poverka timestamp without time zone,
  CONSTRAINT pk_id_sensors PRIMARY KEY (id)
);
 
 
CREATE TABLE users
(
  id serial NOT NULL,
  permission integer,
  "login" text NOT NULL,
  "password" text NOT NULL,
  fam text NOT NULL,
  "name" text,
  otchestvo text,
  tel text,
  email text,
  CONSTRAINT pk_user PRIMARY KEY (id),
  CONSTRAINT unik_login UNIQUE (login)
);
 
 
CREATE TABLE users_rooms
(
  id serial NOT NULL,
  id_users integer,
  id_rooms integer,
  start timestamp without time zone,
  enddata timestamp without time zone,
  CONSTRAINT pk_id_users_rooms PRIMARY KEY (id),
  CONSTRAINT fk_id_users_rooms FOREIGN KEY (id_rooms)
      REFERENCES rooms (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT fk_id_users FOREIGN KEY (id_users)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
);
 
CREATE TABLE rooms_sensors
(
  id serial NOT NULL,
  id_rooms integer,
  id_sensors integer,
  start timestamp without time zone,
  enddata timestamp without time zone,
  CONSTRAINT pk_id_rooms_sensors PRIMARY KEY (id),
  CONSTRAINT fk_id_rooms_sensors FOREIGN KEY (id_rooms)
      REFERENCES rooms(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT fk_id_sensors_rooms FOREIGN KEY (id_sensors)
      REFERENCES sensors(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
);
 
 
CREATE TABLE organizations_rooms
(
  id serial NOT NULL,
  id_organizations integer,
  id_rooms integer,
  start timestamp without time zone,
  enddata timestamp without time zone,
  CONSTRAINT pk_id_org_rooms PRIMARY KEY (id),
  CONSTRAINT fk_id_organizations FOREIGN KEY (id_organizations)
      REFERENCES organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT fk_id_rooms_organizations FOREIGN KEY (id_rooms)
      REFERENCES rooms(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
  
);

Сообщение отредактировано: Kitty - 27.04.15, 10:28

Это уже ближе к истине, но всё ещё далеко от неё.
У Вас всё ещё присутствуют самостоятельные сущности, под хранение которых не выделены таблицы. И соответственно нет таблиц связей.
Вот пара вопросов.
Как Вы полагаете, номер телефона существует сам по себе, или для его существования обязательно необходимо существование в этом мире организаций?
Как вы полагаете, сущность person, являющаяся атрибутом сущности организация, и сущность person, являющаяся атрибутом сущности комната - это разные сущности? или и то, и другое есть просто экземпляры сущности Работник?

Скрытый текст

Может, всё-таки почитаете по теме ХОТЬ ЧТО-НИБУДЬ?

Лекция № 10. Нормальные формы
4.2.Теория нормальных форм.

Добрый день.
Помогите еще разобраться в последовательности шагов:
1. Вставляю запись в таблицу organizations.
2. Вставляю запись в таблицу rooms.
В интерфейсе программы предлагаю при вставки в таблицу rooms выбрать организацию из списка. В списке организации из таблицы organizations. Если пользователь выбрал организацию то ее id serial будем использовать для таблицы связи organizations_rooms.
Как теперь правильно организовать вставку в таблицу связи organizations_rooms (вставить новую комнату id serial и id serial из пункта №2)?
Нужно придумать триггер на вставку в таблицу rooms, который вставлял бы запись в таблицу связи organizations_rooms?
Спасибо.

НЕТ.
Заполнение таблицы комнат - совершенно отдельная самостоятельная операция, не имеющая никакого отношения к остальным процессам. И выполняемая в отдельной форме.
Заполнение таблицы организаций - совершенно отдельная самостоятельная операция, не имеющая никакого отношения к остальным процессам. И выполняемая в отдельной форме.
Установление соответствия между комнатой и организацией, которая её занимает - это тоже отдельная операция, выполняемая своей формой. Она может быть как отдельной формой, так и подчинённой формой на форме организаций. Хотя я бы рекомендовал первое.
Ни на какой стадии ЭТОГО процесса (создание записей комнат, создание записей организаций, установление соответствий между ними в таблице занимаемых помещений) триггеры нахрен не нужны.