Проблемы проектирования базы данных с отношениями -- php поле с участием mysql поле с участием database-design поле с участием foreign-keys поле с участием relational-database пол Связанный проблема

Database Design Issues with relationships


1
vote

проблема

русский

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

Основная логика предусматривает следующее:

  1. Пользователи получают доступ к интернет-тренировкам посредством сидений. Пользователи могут иметь несколько сидений.
  2. сиденья приобретаются компаниями и имеют отношение к многим ко многим с продуктами.
  3. продукт имеет отношение к многим ко многим с модулями.
  4. модуль имеет отношение к многим ко многим с уроками.
  5. Уроки - это конечные пользователи доступ к их обучению.
  6. для грязных вод, по тому или иначе у некоторых пользователей есть несколько мест, которые содержат те же продукты.
  7. Сертификация
  8. происходит на основе на основе продукта, а не на основе на основе.
  9. пользователи имеют отношения со многими ко многими со уроками, которые хранят свой текущий статус или оценка для урока.
  10. Пользователи подтверждают для продукта, когда они завершают все уроки во всех модулях для продукта.
  11. Это также важно знать, когда все уроки для конкретного модуля завершены пользователем.
  12. Некоторые места будут для повторного размещения, что означает, что пользователи, которые ранее сертифицированы для продукта, могут зарегистрироваться и сделать экзамен по перетекации.
  13. из-за правила 11 пользователи могут и будут иметь несколько записей сертификации.
  14. Редактировать: Когда пользователь завершает урок (оценки лучше, чем 80%), то пользователь имеет (в соответствии с текущей бизнес-логикой) завершил урок для всех продуктов и всех сидений, которые содержат урок.

Беда, которые я продолжаю в том, что я поддерживаю с текущим дизайном и бизнес-логикой, так как я более или менее описал, заключается в том, что я не могу найти способ эффективно завязать, сертифицировал ли пользователь для конкретного продукта и места против, когда у них нет. Я продолжаю ударить наглыми навязками, пытаясь установить, какие продукты, под которыми сидения были сертифицированы для пользователя и у которых нет. Часть проблемы состоит в том, что если они в настоящее время зарегистрированы на множестве одного и того же продукта под разными сиденьями, то я должен подсчитать товар только один раз.

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

Вы можете просмотреть соответствующую часть схемы базы данных здесь: http://lpsoftware.com/problem_db_structure.png

Английский оригинал

I'm working on an upgrade for an existing database that was designed without any of the code to implement the design being considered. Now I've hit a brick wall in terms of implementing the database design in code. I'm certain whether its a problem with the design of the database or if I'm simply not seeing the correct solution on how to what needs to be done.

The basic logic stipulates the following:

  1. Users access the online trainings by way of Seats. Users can have multiple Seats.
  2. Seats are purchased by companies and have a many-to-many relationship with Products.
  3. A Product has a many-to-many relationship with Modules.
  4. A Module has a many-to-many relationship with Lessons.
  5. Lessons are the end users access for their training.
  6. To muddy the waters, for one reason or another some Users have multiple Seats that contain the same Products.
  7. Certification takes place on a per Product basis, not on a per Seat basis.
  8. Users have a many-to-many relationship with lessons that stores their current status or score for the lesson.
  9. Users certify for a Product when they complete all of the Lessons in all of the Modules for the Product.
  10. It is also significant to know when all Lessons for a particular Module are completed by a User.
  11. Some Seats will be for ReCertification meaning that Users that previously certified for a Product can sign up and take a recertification exam.
  12. Due to Rule 11, Users can and will have multiple Certification records.
  13. Edit: When a User completes a Lesson (scores better than 80%) then the User has (according to the current business logic) completed the Lesson for all Products and all Seats that contain the Lesson.

The trouble that I keep running into with the current design and the business logic as I've more or less described it is that I can't find a way to effectively tie whether a user has certified for a particular product and seat vs when they have not. I keep hitting snags trying to establish which Products under which Seats have been certified for the User and which haven't. Part of the problem is because if they are currently registered for multiple of the same Product under different Seats, then I have to count the Product only once.

Below is a copy of the portion of the schema that's involved. Any suggestions on how to improve the design or draw the association in code would be appreciated. In case it matters, this site is built on the LAMPP stack.

You can view the relevant portion of the database schema here: http://lpsoftware.com/problem_db_structure.png

</div
              
         
         

Список ответов

2
 
vote
vote
Лучший ответ
 

Что вы ищете, это реляционное разделение Не реализован непосредственно в SQL, но это можно сделать. Поиск Google для других примеров.

 

What you're looking for is relational division Not implemented directly in SQL, but it can be done. Search google for other examples.

</div
 
 
0
 
vote

После быстрого взгляда на схему я думаю, что одна из вещей, которые вы можете сделать, это создать таблицу «to_be_certified». Заполните его с помощью user_id, product_id и seat_id, когда продукт назначен на сиденье (когда product_seat_rtab заполняется).

При добавлении записи в таблицу сертификации_rtab, удалите соответствующую запись в таблице «to_be_certified». Это даст вам легкий доступ ко всем продуктам, которые сертифицированы для пользователей и те, которые нет.

Чтобы избавиться от дубликата Product_ids, вы можете группировать по продукту_ид.

 

After a quick look at the schema I think one of the things you can do is create a 'to_be_certified' table. Populate it with user_id, product_id and seat_id when a product is assigned to a seat (when product_seat_rtab is populated).

On adding a record to the certification_rtab table, delete the corresponding record in the 'to_be_certified' table. This will give you an easy access to all the products which are certified for a users and the ones that are not.

To get rid of duplicate product_ids, you can group by product_id.

</div
 
 
0
 
vote

Вам нужно внести изменения в таблицу rotensstatus_rtab:

 <код> CREATE TABLE lessonstatus_rtab (   user_id    INT NOT NULL,   seat_id    INT NOT NULL,   lesson_id  INT NOT NULL REFERENCES lesson_rtab,   accessdate TIMESTAMP,   score      NUMERIC(5,2) NOT NULL DEFAULT 0,   PRIMARY KEY (user_id, seat_id, lesson_id),   FOREIGN KEY (user_id, seat_id) REFERENCES user_seat_rtab (user_id, seat_id) );   

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

 <код> SELECT p.name, us.user_id, us.seat_id, COUNT(l.id) = COUNT(lu.lesson_id) AS is_certified FROM user_seat_rtab AS us  JOIN seat_rtab AS s ON (s.id = us.seat_id)  JOIN product_seat_rtab AS ps ON (ps.seat_id = s.id)  JOIN product_rtab AS p ON (p.id = ps.product_id)  JOIN product_module_rtab AS pm ON (pm.product_id = p.id)  JOIN module_rtab AS m ON (m.id = pm.module_id)  JOIN module_lesson_rtab AS ml ON (ml.module_id = m.id)  JOIN lesson_rtab AS l ON (l.id = ml.lesson_id)  LEFT OUTER JOIN lessonstatus_rtab AS lu     ON (lu.lesson_id = l.id AND lu.user_id = us.user_id       AND lu.seat_id = us.seat_id AND lu.score > 0.50) GROUP BY p.id, us.user_id, us.seat_id;   
 

You need to make changes to the lessonstatus_rtab table:

CREATE TABLE lessonstatus_rtab (   user_id    INT NOT NULL,   seat_id    INT NOT NULL,   lesson_id  INT NOT NULL REFERENCES lesson_rtab,   accessdate TIMESTAMP,   score      NUMERIC(5,2) NOT NULL DEFAULT 0,   PRIMARY KEY (user_id, seat_id, lesson_id),   FOREIGN KEY (user_id, seat_id) REFERENCES user_seat_rtab (user_id, seat_id) ); 

Then you can query for each product that a user has a seat for, is he certified? This presumes that the number of lessons he has scored, say, 50% or higher is the same as the number of lessons in all modules for the product.

SELECT p.name, us.user_id, us.seat_id, COUNT(l.id) = COUNT(lu.lesson_id) AS is_certified FROM user_seat_rtab AS us  JOIN seat_rtab AS s ON (s.id = us.seat_id)  JOIN product_seat_rtab AS ps ON (ps.seat_id = s.id)  JOIN product_rtab AS p ON (p.id = ps.product_id)  JOIN product_module_rtab AS pm ON (pm.product_id = p.id)  JOIN module_rtab AS m ON (m.id = pm.module_id)  JOIN module_lesson_rtab AS ml ON (ml.module_id = m.id)  JOIN lesson_rtab AS l ON (l.id = ml.lesson_id)  LEFT OUTER JOIN lessonstatus_rtab AS lu     ON (lu.lesson_id = l.id AND lu.user_id = us.user_id       AND lu.seat_id = us.seat_id AND lu.score > 0.50) GROUP BY p.id, us.user_id, us.seat_id; 
</div
 
 
0
 
vote

Обновление:

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

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

 <код> DROP TABLE user_seat_rtab; RENAME TABLE certification_rtab TO something_different;   

Альтернатива для дальнейшего нормализации этой новой структуры будет делать что-то подобное:

 <код> ALTER TABLE user_seat_rtab      DROP PRIMARY KEY;     ADD COLUMN product_id int(10) unsigned NOT NULL;     ADD CONSTRAINT pk_user_seat_product PRIMARY KEY (user_id, seat_id, product_id);     ADD CONSTRAINT fk_product_user_seat FOREIGN KEY (product_id) REFERENCES product_rtab(id) ON DELETE RESTRICT;   

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

 

UPDATE:

I have considering this issue further and have considered whether it would allow things to work better to simply remove the user_seat_rtab table and then use the equivalent certification_rtab table (probably renamed) to hold all of the information regarding the status of a user's seat. This way there is a direct relationship established between a User, their Seat, each Product within the Seat, and whether the User has certified for the particular Product and Seat.

So I would apply the following changes to the schema posted with the question:

DROP TABLE user_seat_rtab; RENAME TABLE certification_rtab TO something_different; 

An alternative to further normalize this new structure would be to do something like this:

ALTER TABLE user_seat_rtab      DROP PRIMARY KEY;     ADD COLUMN product_id int(10) unsigned NOT NULL;     ADD CONSTRAINT pk_user_seat_product PRIMARY KEY (user_id, seat_id, product_id);     ADD CONSTRAINT fk_product_user_seat FOREIGN KEY (product_id) REFERENCES product_rtab(id) ON DELETE RESTRICT; 

I'm not really certain whether this would solve the problem or if it will just change the nature of the problem slightly while introducing new ones. So, does anyone have any other criticisms or suggestions?

</div
 
 

Связанный проблема

0  Выберите значения, не включенные в таблицу  ( Select values not included in table ) 
Мой друг делает базу данных на MS Access, и он столкнулся с следующей проблемой. в своей базе данных, у него есть таблица под названием «Бронирование». Эта ...

1  Должны ли соединительные таблицы иметь более одного первичных ключа от другой идентифицирующей таблицы?  ( Should junction tables have more than one primary keys from another identifying ) 
Вот пример: изначально у меня 3 столы. Таблица B Ссылки на таблицу А. Итак, теперь в таблице B имеет два основных ключа. Один используется в качестве оригинал...

1  Выберите ряд, чтобы столбец2 не равна столбцу1  ( Select row that column2 not equal to column1 ) 
Мне нужна помощь с запросом SQL У меня есть эта таблица <код> likes friend friend2 1 2 2 1 3 1 4 ...

-3  Что быстрее для Stata: манипулирование данными в плоской базе данных (I.e. Excel) или в реляционной базе данных?  ( Whats faster for stata manipulating data in a flat database i e excel or in ) 
Я аналитик по оптимизации начального уровня в компании, который публикует данные о рисках для различных компаний. У нас есть тонны данных (до такой степени, ч...

1  Есть ли канонические формы для запросов баз данных?  ( Are there canonical forms for database queries ) 
сказать, что я хочу сделать «оптимизированный генератор запросов». В основном оптимизатор SQL Query, который намного лучше, чем то, что можно поместить в SQL ...

-1  Как использование и ключевое слово отличается от использования «объединений» [явно] в SQL [Duplicate]  ( How is using and keyword different from using joins explicitly in sql ) 
<в сторону CLASS = "S-NEWACTS S-WELTIVE__info JS-Post-New Imide MB16« Роль = «Статус»> Этот вопрос уже есть ответы здесь : ...

1  Как реализовать отношения в одну ко многим с требованием «является текущим»  ( How to implement a one to many relationship with an is current requirement ) 
Проектирование базы данных, есть отношения между двумя таблицами, <код> Job и <код> Document . Один <код> Job может иметь несколько <код> Documents , но оди...

-1  Могу ли я сделать рефлексивную ассоциацию и нормальную ассоциацию  ( Can i make a reflexive association and normal association ) 
Мой заголовок не так конкретный, как и должен, потому что я не знаю, как его сформулировать. ОК, так что я удивляюсь: могу ли я или не создавать в реляционн...

4  Таблица членов SQL, чтобы показать семейные отношения между членами  ( Sql members table to show family relationships between members ) 
Я пытался в течение нескольких дней, чтобы разработать эту базу данных. Я проектирую его в доступе, затем реализующий на MySQL Server в качестве веб-приложени...

0  Схема базы данных для хранения отправленных сообщений и сообщений, полученных одному и тому же человеку [закрыто]  ( Database schema to store messages sent and messages received to the same person ) 
<в сторону CLASS = "S-NEWACTS S-WELTIVE__info JS-Post-New Imide MB16« Роль = «Статус»> <Путь d = "M15 6.38A6.48 6.48 0 007.78. 04H-.02A6.49 6.49 0 002.05 ...

0  Структура таблицы посещаемости студента  ( Student attendance table structure ) 
Мои извинения за то, что спрашиваете это очень необычный вопрос. Я использую mysql, и у меня есть база данных, которая имеет следующие таблицы <код> cours...

6  Развитие базы данных реляционного лагеря и "реальная"  ( Relational camp and real world database development ) 
Больше десятилетия прошло с момента первой публикации даты и Дарвена "Третий манифест" в 1995. Какое место реляционной школы мысли в современном мире баз ...

0  Свяжите таблицу ответа на стол билетов  ( Relate reply table to ticket table ) 
Предположим, у меня есть билет / вопрос, который может иметь ноль или много ответов. Я могу относиться к таблицам «ответить» и «билет» двумя способами. П...

0  MySQL выберите один из двух полей, где один из них равна значению  ( Mysql select one of two fields where one of them equal a value ) 
У меня есть две таблицы «пользователи» и «отношения», я хочу выбрать отношения всех пользователей. <код> table relations user1 user2 1 2 1 3 5 1...

7  Плюсы / минусы и способы реализации глобально уникального идентификатора в реляционной базе данных?  ( Pros cons of and ways to implement globally unique identifier in relational data ) 
Что касается первой части моего вопроса: я недавно спрашивал себя, каковы преимущества и компромиссы имеют уникальный идентификатор для определенных таблиц в ...

3  Сущность с версией: Оптимальный способ получения списка последней версии с использованием SQL  ( Entity with versioning optimal way of getting a list of last version using sql ) 
Вопрос о SQL на SQL Server 2012. фон: Есть сущность, называемая объектом . Сущность может иметь n n entityversion ( 1: n ). EntityVersion м...

2  Как мигрировать существующие данные, управляемые с помощью SQERYL?  ( How to migrate existing data managed with sqeryl ) 
Есть небольшой проект мой, достигший его выпуска, основанный на squeryl - Whitefafe Relational баз данных Framework для Scala (JVM основанный язык). Я пре...

0  Советы дизайна базы данных - несколько вопросов да или нет  ( Database design advice multiple yes or no questions ) 
I HAM В настоящее время разработал таблицу базы данных и форму VB, которая идет с ней. Форма спросит около 40 предопределенных вопросов да / нет каждому польз...

3  Как настроить основные ключи в отношении?  ( How to set up primary keys in a relation ) 
Я хочу знать, как правильно установить первичные клавиши в отношении . Например. У нас есть ER-диаграмма , которые содержат элементы: . ключевые атрибут...

1  Преобразовать Ollology в реляционную базу данных  ( Convert an owl ontology into a relational database ) 
Есть ли какой-нибудь инструмент, чтобы быстро получить реляционную базу данных из совы онтологии? ...

2  Блокировка базы данных  ( Locking the database ) 
Привет, я пытаюсь увидеть, что блокирует базу данных и нашел 2 типа блокировки. Оптимистичный и пессимистичный замок. Я нашел некоторые статьи на Wiki, но я х...

1  Как мы относимся к столбцу, которая имеет такое же значение во всех записях таблицы?  ( How do we treat a column that has the same value across all records of a table ) 
Предположим, что у меня есть стол и упростить все строки, о <код> Persons и предположим, что мы храним цвет человека. Теперь давайте упростим, что у нас есть...

0  Как получить все дополнительные категории из базы данных в Java  ( How to get all sub categories from database in java ) 
У меня есть родовая структура дерева в моей базе данных, как ниже. <код> mysql> select * from categories; +----+---------------+-----------+ | id | category...

1  Как генерировать модель класса UML из реляционной модели?  ( How to generate uml class model from a relational model ) 
Кто-нибудь знает, можно ли генерировать модель класса UML из реляционной модели базы данных? Как? Редактировать: Чтобы уточнить то, что я считаю моделью к...

0  Yii2 Расширенные отображения и сохранение полей двух таблиц в одной форме  ( Yii2 advanced displaying and saving fields of two tables in one form ) 
Как я могу отображать поля разных таблиц в одной форме и сохранить его одновременно в базе данных ... Сценарий - это когда я получаю новую форму, чтобы заполн...

Связанный проблема

0  Выберите значения, не включенные в таблицу 
1  Должны ли соединительные таблицы иметь более одного первичных ключа от другой идентифицирующей таблицы? 
1  Выберите ряд, чтобы столбец2 не равна столбцу1 
-3  Что быстрее для Stata: манипулирование данными в плоской базе данных (I.e. Excel) или в реляционной базе данных? 
1  Есть ли канонические формы для запросов баз данных? 
-1  Как использование и ключевое слово отличается от использования «объединений» [явно] в SQL [Duplicate] 
1  Как реализовать отношения в одну ко многим с требованием «является текущим» 
-1  Могу ли я сделать рефлексивную ассоциацию и нормальную ассоциацию 
4  Таблица членов SQL, чтобы показать семейные отношения между членами 
0  Схема базы данных для хранения отправленных сообщений и сообщений, полученных одному и тому же человеку [закрыто] 
0  Структура таблицы посещаемости студента 
6  Развитие базы данных реляционного лагеря и "реальная" 
0  Свяжите таблицу ответа на стол билетов 
0  MySQL выберите один из двух полей, где один из них равна значению 
7  Плюсы / минусы и способы реализации глобально уникального идентификатора в реляционной базе данных? 
3  Сущность с версией: Оптимальный способ получения списка последней версии с использованием SQL 
2  Как мигрировать существующие данные, управляемые с помощью SQERYL? 
0  Советы дизайна базы данных - несколько вопросов да или нет 
3  Как настроить основные ключи в отношении? 
1  Преобразовать Ollology в реляционную базу данных 
2  Блокировка базы данных 
1  Как мы относимся к столбцу, которая имеет такое же значение во всех записях таблицы? 
0  Как получить все дополнительные категории из базы данных в Java 
1  Как генерировать модель класса UML из реляционной модели? 
0  Yii2 Расширенные отображения и сохранение полей двух таблиц в одной форме