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

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

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

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

Ключи определяются на стадии анализа - это просто набор однозначно идентифицирующих экземпляр данной сущности атрибутов. В подавляющем большинстве случаев предпочтителен синтетический ключ-счётчик плюс ограничения уникальности.

Внешние ключи также определяются на стадии анализа - при определении связей с учётом логической целостности и непротиворечивости данных.

Kitty тут куча вопросов.
понятно что основные связи это "владеет помещением" и "установлены датчики"
1. только одна организация владеет одним помешением?
2. нужно ли знать историю по связям? какие организации владели помещением вчера, год назад? Какие датчики были установлены?
3. Нужно ли знать план вперед по "овлвдению" помещениями и установке датчиков?
Ошибки
1. При удалении организации из здания помещение не может быть удалено
2. монтаж/демонтаж датчиков в помещении никак не связан (по крайней мере по описанию задачи) с тем, кто владеет данным помещением
С учетом этих замечаний попробуйте нарисовать ER модель и покажите, что получилось

Цитата Павел Калугин @ 23.04.15, 08:10

1. только одна организация владеет одним помешением?

Как минимум организация-владелец, эксплуатирующая организация (а это далеко не всегда владелец!) и организация-арендатор. И на кого из них "вешать" помещения и датчики - тот ещё вопрос...
Правильная схема вылезет из анализа и будет совсем даже не элементарной.

Akina в реальной жизни таки да. Но это же курсовая, которую надо защитить в середине апреля

Цитата Павел Калугин @ 23.04.15, 08:34

это же курсовая

Тогда тем более надо изучить теорию. Чтобы не "плавать" на защите.

Цитата

1. только одна организация владеет одним помешением?

Торговый центр. В нем расположены помещения на разных этажах. Теперь есть организации. Каждая организация владеет разными помещениями. У OOO Вымпел пять помещений на одном этаже. У ООО Пламя три помещения на разных этажах. У ООО Вода одно помещение и т.д.

Цитата

22. нужно ли знать историю по связям? какие организации владели помещением вчера, год назад? Какие датчики были установлены?

Нет не нужо.

Цитата

3. Нужно ли знать план вперед по "овлвдению" помещениями и установке датчиков?

Нет не нужно.

Пользователь в программе создает в базе организации, затем должен закрепить в базе за конкретной организацией ее помещения. Потом монтажники говорят, в каком помещении установлены датчики с их номерами. Внести это в таблицу датчиков. Должно быть все это как-то правильно связано. Выбираю конкретную организацию – вижу ее помещения, выбираю конкретное помещение – вижу в нем датчики.
Если организация удаляется из программы, то я так понимаю, надо удалить все, что с ней связано. Зачем хранить в базе помещения и датчики в этих помещениях, если эта организация больше не существует...
Мне бы хотя бы скелет, какие правильно связующие ключи у этого дела.

Цитата

Но это же курсовая, которую надо защитить в середине апреля

Нет не курсовая, просто построения структур базы вызывает проблемы т.к. воображалки не хватает.

И на что тут может не хватать воображалки?
Организация (1 ко много) Помещение (1 ко много) Датчик

Цитата Kitty @ 23.04.15, 11:09

связующие ключи

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

Вот так примерно?

CREATE TABLE organizations
(
  ideserial serial NOT NULL,
  nameorganization character varying(200) NOT NULL,
  tel1 character varying(20),
  tel2 character varying(20),
  prim1 character varying(200),
  prim2 character varying(200),
  idorganizations integer,
  CONSTRAINT idserialorganizations PRIMARY KEY (ideserial),
  CONSTRAINT nameorganizations UNIQUE (nameorganization)
)
 
CREATE TABLE rooms
(
  idserial serial NOT NULL,
  nameroom character varying(200),
  nameorganization character varying(200) NOT NULL,
  idroom integer,
  idcustomer integer,
  prim character varying(200),
  floor character varying(12),
  CONSTRAINT idrooms PRIMARY KEY (idserial),
  CONSTRAINT fktoorganizations FOREIGN KEY (nameorganization)
      REFERENCES organizations (nameorganization) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT unikname UNIQUE (nameorganization)
)
 
CREATE TABLE sensors
(
  idserial serial NOT NULL,
  nameorganization character varying(200) NOT NULL,
  id integer NOT NULL,
  lastdt timestamp without time zone,
  roomid integer,
  prim character varying(128),
  idroom integer,
  nameroom character varying(200),
  CONSTRAINT sensorsid PRIMARY KEY (idserial),
  CONSTRAINT fktorooms FOREIGN KEY (nameorganization)
      REFERENCES rooms (nameorganization) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT sensors_nameorganization_key UNIQUE (nameorganization)
)

Ну если очень примерно - то да. Исключительно в части имён таблиц и формирования первичных и внешних ключей.

Хотя я бы предложил не бежать вперёд паровоза, и заняться теорией. Хотя бы чтобы понимать, что эта структура не сильно отвечает 3НФ. Дублирование на каждом шагу, плюс потенция рассогласования данных.

Во всяком случае с такой структурой даже курсовую не сдать - это стопудово. А о боевой системе даже речи идти не может.

Сообщение отредактировано: Akina - 23.04.15, 11:46

Ну если очень примерно - то да.

Спасибо.

Дублирование на каждом шагу, плюс потенция рассогласования данных.

Собственно поэтому и обратилась за помощью здесь.
Подредактируйте, пожалуйста.

У меня хрустальный шар в ремонте, а догадываться, что будет записано в какое поле, тупо лень. Уж не поленитесь...
А для начала уберите поля, которые есть в таблице на стороне "один", из таблицы на стороне "много".
И уберите каскадное удаление - если организация съедет, это не значит, что датчики демонтируют и комнату сломают.

Цитата

И уберите каскадное удаление - если организация съедет, это не значит, что датчики демонтируют и комнату сломают.

Теперь поняла.

Цитата

Уж не поленитесь...

Кол-во и название полей еще не определено. Это я просто накидала разных полей для тестирования каскадного удаления. Мне самой надо придумать правильные поля для всех трех таблиц, чтобы было информативно и удобно потом пользоваться.
В простом случае можно упростить все до такого:
В таблице организаций - только названия организаций. Наверно оно должно быть уникальным?
В таблице помещений - только названия помещений/или может уникальный номер помещения (ну и к примеру этаж где это помещение).
В таблице датчиков - уникальный серийный номер датчика, в каком он помещении.
Теперь в интерфейсе программы надо понимать: организация->ее помещения->ее датчики в этих помещениях. При выборе конкретного помещения знать сколько в нем датчиков и какие это датчики по их номеру.

При таком минимальном кол-ве полей, как бы вы организовали?
Спасибо.

Сообщение отредактировано: Kitty - 23.04.15, 12:34

Цитата Kitty @ 23.04.15, 12:20

В таблице организаций - только названия организаций. Наверно оно должно быть уникальным?

Сфига бы? Вот пара ИНН+КПП - та уникальна. А одноимённых организаций аки грязи.

Цитата Kitty @ 23.04.15, 12:20

В таблице помещений - только названия помещений/или может уникальный номер помещения

Номер. И для справки - название.

Цитата Kitty @ 23.04.15, 12:20

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

А для этого надо изучить, что такое нормальные формы, и выполнить анализ предметной области. Работайте.

Цитата Kitty @ 23.04.15, 12:20

как бы организовали?

Не скажу. Мне пофиг, создадите Вы свою БД или нет. Так что делать её ЗА ВАС, пусть даже эта просьба и высказана в такой форме - не буду.
Изучайте. И только потом делайте - кавалерийским наскоком, без теории и понимания, как и почему делать, не получится. Вот с проблемами или непонятками - но совершенно конкретными, - милости просим. Объясним, как нужно, и почему именно так.
Ну а коли не хотите изучать, хотите сразу готовую БД - то Вы ошиблись форумом. С таким подходом - во фриланс.

Akina сегодня не с той ноги встал...

#SI#
Да надоели уже студенты вусмерть... и все, как один, в пеной у рта - у меня реальная задача, я не халявщик... ага. А то, что ни одна приличная фирма не станет монтировать систему датчиков без аппаратуры управления и контроля, и плюс обязательно управляющий софт, в котором есть всё вышеописанное на профессиональном уровне, как-то забывается. Про то, что подобные системы вообще-то начинаются с серьёзного ТЗ на проектирование и, соответственно, проекта с миллионом согласований, что они создаются на годы и документируются по самое не могу, ибо за малый косяк можно на несколько лет загреметь в места не столь отдалённые - не, не слышали...

При этом сами или вообще ничего не делают, или несут такую пургу, что сразу видно, чем занимались весь семестр вместо посещения лекций.

Цитата Kitty @ 23.04.15, 12:20

В таблице организаций - только названия организаций. Наверно оно должно быть уникальным?

Не обязательно. Я бы выбрал фискальный код организации, они точно разные.

Цитата Kitty @ 23.04.15, 12:20

В таблице помещений - только названия помещений/или может уникальный номер помещения (ну и к примеру этаж где это помещение).

Туалеты, подсобные помещения,коридоры на всех этажах уникальные - не уверен. Я думаю что их там несколько.
Хотя Вы можете и установить такую уникальность для себя (номер коридора и сколько сантиметров от правого угла здания с севера).

Цитата Kitty @ 23.04.15, 12:20

В таблице датчиков - уникальный серийный номер датчика, в каком он помещении.

В таблице датчиков все что касается датчиков (серийный номер, год выпуска,гарантия, фирма изготовитель,уникальный ключ(ваш внутренний id))

И таблицы где все эти связи вместе датчики с помещениями, помещения с фирмами.

Фирма переехала в другое помещение, вы только привязываете помещение с фирмой, а датчики сами с фирмой свяжутся (ели они там есть)

Сообщение отредактировано: Bas - 23.04.15, 13:51

Да надоели уже студенты вусмерть... и все, как один, в пеной у рта - у меня реальная задача, я не халявщик... ага.

Да не студентка я! #SI#, подтвердит, он меня давно знает.
На форуме не была пару лет.
Давно с базами дела не имела. Мне при наличии правильной структуры БД довольна просто написать к ней интерфейс на С++ Builder. А вот "родить" структуру БД трудно, мучительно трудно.
Bas, спасибо.

Буду пробовать применить все рекомендации из этой темы.

Сообщение отредактировано: Kitty - 23.04.15, 13:59

Kitty в любом случае я рекомендовал бы пойти по следующему пути.
есть три сущности
Организации
-ID организации
-поля описывающие организацию
Помещения
-ID помещения
-поля описывающие помещение

Приборы (датчики)
-ID прибора
-поля описывающие прибор

есть связи
Арендует помещение
-ID организации
-ID помещения
-Дата с
-Дата по

Установлен в помещении
-ID помещения
-ID прибора
-Дата с
-Дата по

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

Что в такой структуре значит "прекратил аренду" - нет связи "Арендует помещение" на дату между организацией и любым помещением. То есть пользователь в интерфейсе к ПО (никак не администратор бд) должен указать что дата окончания аренды - Х

Та же самая логика с датчиками (приборами) Установка их никак не связана с "арендой". В реальной жизни установка или замена осушествляется на основании договоров. Следовательно удалять что куда было установлено/перемещено никак нельзя.

Еще раз повторю просьбу - нарисуйте ER диаграмму.
Потом ,как нарисуете, примените к сущностям алгоритмы нормализации


Добавлено 23.04.15, 14:27

Цитата Bas @ 23.04.15, 13:47

Не обязательно. Я бы выбрал фискальный код организации, они точно разные.

Для одной организации может быть больше одного кода за период времени. Только "сурогатный ключ"

Цитата Kitty @ 23.04.15, 13:58

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

А подход к задаче как у студента двоешника

Сообщение отредактировано: Павел Калугин - 23.04.15, 14:29

Цитата Kitty @ 23.04.15, 13:58

Да не студентка я! #SI#, подтвердит, он меня давно знает.

Подтверждаю!

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

Леночка!

Добавлено 23.04.15, 18:40

Цитата Павел Калугин @ 23.04.15, 14:25

А подход к задаче как у студента двоешника

Паша! Слазий в оракловый раздел. Почитай мои темы с начала прошлого года. Скажешь то же самое. А мне, всего-навсего, подсунули древнюю оракловую базу с оболочкой на 97-м Акцессе без единой строчки документации.

Цитата Павел Калугин @ 23.04.15, 14:25

Для одной организации может быть больше одного кода за период времени.

Зависит от законодательства. У нас ,сейчас, не может быть у одной организации два INN или IDNP.

Цитата Павел Калугин @ 23.04.15, 14:25

Только "сурогатный ключ"

Уже обсуждали,Сурогатный или естественный ключ, Продолжение
Однозначная идентификация записей в, таблице, но не PK.

Цитата Bas @ 24.04.15, 05:58

У нас ,сейчас, не может быть у одной организации два INN или IDNP.

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

Цитата Bas @ 24.04.15, 05:58

Зависит от законодательства. У нас ,сейчас, не может быть у одной организации два INN или IDNP.

Одновременно не может. А при перерегистрации или реорганизации еще как может... А дальше от бизнеслогики зависит после перерегистрации это абсолютно новая организация или таже но с новым ИНН
Ну и про "филиалы" под одним ИНН Akina верно пишет

#SI#, ну так убеди Леночку не с шашкой наперевес create table махать сразу, а на бамаське порисовать структурки. Благо задача копеешная и тетрадного листка хватит варианта на 3-4.

Цитата Akina @ 24.04.15, 06:17

У вас - это в какой стране?

Молдова.

Цитата Павел Калугин @ 24.04.15, 07:08

Ну и про "филиалы" под одним ИНН Akina верно пишет

Филиалы имеют ИНН как и у головного, но названия разные и имеют код местности (присвоенный налоговой, у головного это 000).
Расчеты ведут через РЦ головного офиса.
Ключ может быть Наименование+ИНН , ИНН+код местности.

Добавлено 24.04.15, 08:56
Kitty,в одном помещении могут находиться много фирм? Арендуют по два квадратных метра, чтобы стол поставить.

Сообщение отредактировано: Bas - 24.04.15, 08:56

Цитата Павел Калугин @ 24.04.15, 07:08

#SI#, ну так убеди Леночку не с шашкой наперевес create table махать сразу, а на бамаське порисовать структурки

Она ж честно написала:

Цитата Kitty @ 23.04.15, 13:58

"родить" структуру БД трудно, мучительно трудно

Господа, судя по вашим вопросам, задачка таки совсем непростая...

Цитата Bas @ 24.04.15, 08:47

Ключ может быть Наименование+ИНН , ИНН+код местности.

Если в законодательном акте однозначно обусловлена уникальность некоей комбинации - да. Иначе - нет, синтетический ключ и, если прёт, требование уникальности.

Цитата #SI# @ 24.04.15, 09:48

судя по вашим вопросам, задачка таки совсем непростая...

Если делать через опу - конечно, непростая! А если по науке - то там нехрен делать вообще! Но мы ж птицы гордые, теорий изучать не желаем и анализ проводить не будем, а поскольку высасывание структуры из пальца не сработало - мы создадим тему, подначим пару знающих, и нам всё сделают без всяких там наук и теорий...

#SI# задача тривиальная. Решается в два притопа три прихлопа. Собственно львиная доля решения уже изложена выше.
Параллельно опять поднят спор про естественные и суррогатные ключи

Цитата

Kitty,в одном помещении могут находиться много фирм?

Одна.

Цитата

то там нехрен делать вообще!

Поэтому надеялась на помощь. Знала, что для гуру это 5-ть минут.

Цитата

мы создадим тему, подначим пару знающих, и нам всё сделают без всяких там наук и теорий...

Да, тему создавала для знающих людей. Нарушила правила, удалите Вы эту тему и всех делов.

Сообщение отредактировано: Kitty - 24.04.15, 13:18

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

Бабы! Заткни уши!!! ............................................. © к/ф Председатель

Ещё раз повторяю - сам попал в такую же ситуацию полтора года назад. Разница с этим разделом в том, что ораклоиды на форуме - люди спокойные и вежливые.
---
А ещё иногда элементарно не хватает времени...

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

http://www.youtube.com/watch?v=jBDF04fQKtQ

Сообщение отредактировано: #SI# - 24.04.15, 12:16

2 #SI#

И много там у ораклистов нерадивых студентов? да полтора в год от силы, включая тех, кто решил на работе до кучи его освоить, ибо под рукой есть, грех не воспользоваться. И ещё полтора - начинающие. Кстати, даже среди таких - большинство не скажет "не нужна мне теория, мне сделать и забыть".

Плюс ещё одно. Я понимаю, когда в теме сходу заявляется "СУБД - Оракл, без вариантов" - это означает, что Оракл уже имеется физически, и всё по-взрослому. Но когда аналогично озвучивается постгресс/файрбёрд/мускул/мсде, сомнения у меня просто-таки чешутся... как человек, который в базах данных ну вообще ни ухо ни рыло, сделал такой уверенный выбор? да элементарно - в 99% случаев это выбор преподавателя (и зачастую потому, что с другими диалектами у того явные проблемы).

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?
Спасибо.

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

Цитата Kitty @ 28.04.15, 07:35

2. Вставляю запись в таблицу rooms.

Если нет организации то и комнаты нет?
Если есть комната то и организация должна быть?
Может ли быть пустая комната, еще не занятая никем?
Организация есть, но комнаты для нее нет, или она еще только желает взять комнату но площадь ее не устраивает, или цена аренды высока - как вы ее зарегистрируете ?
Ответы на вопросы помогут, может быть.

Сообщение отредактировано: Bas - 28.04.15, 08:30

Понятно. Спасибо.

Цитата Akina @ 24.04.15, 06:17

У нас в одном здании сидит несколько организаций, имеющих один ИНН.

Могу ошибаться, но ИМХО, наиболее правильным будет уникальный ОГРН. Организации вправе именовать свои филиалы как вздумается, но в отчетности они будут фигурировать исключительно согласно гос.регистраци.

По поводу "индескации" номеров комнат - наиболее обобщенный вариант - VARCHAR. Многие используют "поэтажную" нумерацию, многие сквозную, многие вообще могут присвоить комнате номер "Щитовая №1", "Склад №2". И никому ниче не докажешь.

Цитата JoeUser @ 28.04.15, 15:23

ИМХО, наиболее правильным будет уникальный ОГРН.

Цитата JoeUser @ 28.04.15, 15:23

номеров комнат - наиболее обобщенный вариант - VARCHAR

Использование в качестве первичного индекса строковых типов, в общем, плохая идея. Как потому, что синтетический чисельный ключ более компактен, так и потому, что уникальность строк есть величина непостоянная, зависящая от charset и collation. Разумнее именно синтетический ключ и, если необходимо, UNIQUE KEY.
Случаи, когда натуральный ключ оправдан, на самом деле достаточно редки.

Цитата Akina @ 28.04.15, 15:30

Случаи, когда натуральный ключ оправдан, на самом деле достаточно редки.

Согласен. Я и написал - "обобщенный вариант". Просто когда у построителей тех.паспорта объекта "зашкаливает" - это чревато гемором у разработчика ПО.

Добавлено 28.04.15, 15:40
По поводу зданий, комнат, датчиков ... Просто имел и имею дело с одной относительно "уникальной" организацией - бизнесцентром. Напишу ряд "аксиом", чтобы было в памяти и на слуху...

1) У здания может быть только один владелец
2) Собственниками помещений в здании могут быть несколько организаций/юрлиц (выкуп)
3) Помещения могут быть собственностью, могут быть арендой
4) Датчики могут быть централизованными, могут быть собственностью
- помещение в собственности собственника1, датчики - собственность владельца здания
- помещение в собственности собственника1, датчики - собственность собственника1
- помещение в аренде арендатора1, датчики - собственность владельца здания
- помещение в аренде арендатора1, датчики - собственность арендатора1

Цитата JoeUser @ 28.04.15, 15:34

У здания может быть только один владелец

Да ладно... тебе что, незнаком термин "долевая собственность"? а если сюда ещё и доверительное управление приплести, совсем веселуха начнётся. Но все эти факты выпадают за пределы рассматриваемых в строящемся приложении бизнес-процессов.

Цитата Akina @ 28.04.15, 15:44

Да ладно... тебе что, незнаком термин "долевая собственность"?

Не не не - не путай. Собственниками в долях могут быть хоть триста организаций. Владельцем именно здания - одна. Как правило под это дело тупо создается юрлицо с уставными долями тех самых собственников. На 100% гарантировать не могу сказанное, три-четыре идентичных примера было в процессе работы.

Просто по логике - здание это неделимая сущность недвижимости (не путать с помещениями здания).

Спасибо за юридический ликбез, но ...... это автору не нужно, пока.

Сообщение отредактировано: Bas - 29.04.15, 06:04

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

По поводу схемы, я просто рисую на бумаге связи, мне надо приложить скан своего листочка?

Именно! И никаких скриптов пока на этих листочках не будет все гладко и стройно.

Добавлено 01.05.15, 09:03

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

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

Игнорируете. И критиковать то нечего ибо нет основы.
Если не знаете с чего начать надо начинать по учебнику
1. Перечислить ВСЕ термины
2. Определить что есть сущности, что есть связи и что есть атрибуты связей
3. нарисовать ЕR модель
4. "Окончательно" определить атрибутику всех сущностей и связей.
5. Провести нормализацию
6. Сформулировать основные запросы и проверить что в полученной структуре "ответ" будет получен легко и ненавязчиво
7. Изучить предметную область и подумать что еще завтра захочет пользователь
8. по результатам 6 и 7 вернутся и повторить 1-5
9. Выбор СУБД
10. Написание скриптов

Вы же сразу кодируете. Это основная ошибка в проектировании БД.
Если есть желание можете числу к 10 подготовить по п. 1-8 и продолжим. Ибо до тех пор у меня забота где палатку ставить и куда завтра двигать