How do I represent a schema diagram of my MongoDB database?

I have a MongoDB database that I want to properly document its schema design. I know that MongoDB is a NoSQL database and is schemaless by nature, but I do enforce a schema through my application and I want to represent it in a better way than a print of a findOne() result.

I see many people using ER or UML, but I don’t feel that’s conceptually right to represent my NoSQL database as a relational DB or, at least, it looks weird.

Example using UML: MongoDB: How to represent a schema diagram in a thesis?

I thought that people would be using different models. I’ve searched and the far that I’ve saw was MongoVUE that offers a nice Tree view to understand the schema, but its not printer-friendly.

Is there something else that I’m missing for the NoSQL world? Or should I rest and stick with traditional UML?

¿is good practice this schema for a nosql database in mongodb?

i would like to know if i am right with the next schema, i hope you understand what i want to explain.

This is a little app, that’s would help to students to manage their school grades, i was thinking of create a new collection for each new user,like:

-new user “newUser01”

-create a collection whit the name “newUser01”.

-and store all the data for that user in their own collection.

of course following a data structure for each collection, for make the proper query.

but, i think that would be a bad practice, what do you think?


And how i can create a general class for make queries to the proper collection of each logged user.

(I will use nodejs for the backend with express and typescript)

i use to work with express/typescript in this way: https://medium.com/@tomanagle/strongly-typed-models-with-mongoose-and-typescript-7bc2f7197722

for each collection i create a class for make queries to the proper collection, but with the schema that i already explain above, this is not longer a proper way, with mongodb in the cli i can do something like:

db.getCollection("newUser01").find({}) 

and with mongoose, in the callback i can call the method getCollection with the variable db like:

  mongoose.connect("url",(err,db)=>{  db.getCollection("newUser01").find({});  });

But i am working with typescript and strongly typed models, how i can achived my goal?.

Is my database schema going to work? Tracking git issues

My boss wants an automated weekly report on how many git issues have been moved to and from various “lanes” (pipelines) on a virtual kanban board. I wrote a quick python script to get that data using Github and Zenhub APIs, and now I’d like to write that data to disk.

My current plan is to run this script once an hour, and write to a transaction table when I see that an issue has changed pipelines. Here’s a diagram of my current schema: db schema

So my question is, will this schema be able to support a large number of entries in the Transactions table? This will be tracking somewhere between 1,000 – 10,000 issues, with 50 – 1,000 transactions a day.

Some things worth mentioning:

  • There can be two or more issues with the same IssueNumber and different repoIDs (issues are assigned a number when opened starting from 1). So I’d like to use both RepoID and IssueNumber as the primary key for Issues table.
  • I might have the one-to-many arrows backwards, for example I’m trying to say that one RepoID can correspond to many entries in the Issues table but each entry only corresponds to one RepoID.

I’ve never really designed a database before and there aren’t really coworkers that can help me, so I would appreciate any advice or criticism on this schema.

Enumerating all possible associations given a relational schema

Some background

I’m working on a data-intensive UI where state is stored in a Redux container. Anyone who has ever touched Redux knows there’s a lot of repetitive boilerplate to be written in the form of reducers, actions, action creators, selectors (analogous to queries), and so on.

There’s no fun in writing these, especially when there’s a lot of entity types and accompanying relationships involved.

Therefore, the idea is to devise a relational schema together with a code generator to which the heavy lifting can be outsourced.

Problem

Given a (hypothetical) relational schema like the following:

const schema = {   book: {     attributes: ["id", "name", "subGenreId"],     foreignKeys: [       { fromAttribute: "subGenreId", toEntity: "subGenre", toAttribute: "id" }     ]   },    mainGenre: {     attributes: ["id", "name"]   },    subGenre: {     attributes: ["id", "name", "mainGenreId"],     foreignKeys: [       { fromAttribute: "mainGenreId", toEntity: "mainGenre", toAttribute: "id" }     ]   },    author: {     attributes: ["id", "name"]   },    book_author: {     attributes: ["bookId", "authorId"],     foreignKeys: [       { fromAttribute: "bookId", toEntity: "book", toAttribute: "id" },       { fromAttribute: "authorId", toEntity: "author", toAttribute: "id" }     ]   },    seller: {     attributes: ["id", "name"]   },    seller_book: {     attributes: ["sellerId", "bookId"],     foreignKeys: [       { fromAttribute: "sellerId", toEntity: "seller", toAttribute: "id" },       { fromAttribute: "bookId", toEntity: "book", toAttribute: "id" }     ]   } }; 

The goal to generate selectors along these lines (probably forgot a few):

“direct” relationships:

  • books by subGenre id
  • mainGenre by subGenre id
  • subGenre by book id
  • subGenres by mainGenre id

many-to-many relationships (omitted the junction tables themselves):

  • authors by book id
  • books by author id
  • books by seller id
  • sellers by book id

“distant” relationships:

  • authors by mainGenre id
  • authors by seller id
  • authors by subGenre id
  • books by mainGenre id
  • mainGenre by book id
  • mainGenres by author id
  • mainGenres by seller id
  • sellers by author id
  • sellers by mainGenre id
  • sellers by subGenre id
  • subGenres by author id
  • subGenres by seller id

There are also more complex (e.g. circular) relationships like co-authors, but I don’t mind manually writing code for these when they are needed.

How would one go about (computationally, of course) enumerating these associations between entities? Are there pre-existing solutions? I can imagine graph traversal being involved, but perhaps it is better solved using methods I’m not yet aware of.

Table schema in JSON

I am trying to create a rule-based parsing engine, where users can parse text that is stored in a “table-like” layout. The layout is stored in a JSON format in my database, like:

{"1": [     {"row": "My name is"},     {"row": "My age is"} ], "2": [     {"row": "Oliver"},     {"row": "25"} ]} 

A visual example of the above JSON string:

enter image description here

Rule applied:

#1 First rule: a:3:{s:6:"column";s:1:"2";s:7:"pattern";s:9:"/Oliver/i";s:11:"replacement";s:4:"John";}  #2 Second rule: a:3:{s:6:"column";s:1:"2";s:7:"pattern";s:5:"/25/i";s:11:"replacement";s:2:"25";} 

Which is a serialized array, that can be translated into (the first rule):

column: 2 Regex Pattern: /Oliver/i Regex Replacement: John 

Which will simply perform both rules to my table, and transform it into below:

enter image description here

This is how my rules are applied to the actual content:

//RegexTextReplace.php  public function __construct(array $  arguments){     $  this->column = isset($  arguments['column']) ? $  arguments['column'] : false;     $  this->pattern = $  arguments['pattern'];     $  this->replacement = $  arguments['replacement']; }  public function apply(array $  table) : array {    return $  this->applyRule($  table, $  this->column); }   public function applyRule(array $  table, $  column = false): array{     $  out = [];      foreach ($  table as $  col => $  rows) {         if ($  this->column === false || in_array($  col, $  this->column)) {             $  out[$  col] = array_map([$  this, 'rule'], $  rows);         } else {             $  out[$  col] = $  rows;         }     }      return $  out; }   public function rule($  content) : array  {    return preg_replace($  this->pattern, $  this->replacement, $  content); } 

Now this works. I can successfully apply the regex rule to the table (the 2nd column).

My question

I am in doubt whether the JSON schema for the table is the best approach? Or if there is some sort of standard table schema?

Where does fact constellation schema differ from star and snowflake schema?

In the 3 main (for my level) schemas for data warehouse modelling are the star, snowflake and the fact constellation schemas. I understand that the star schema is a purely denormalized schema as the dimension tables are not allowed to be in the normal form, which is where the snowflake schema comes in. In every other aspect both these schemas are identical. However I don’t understand how the fact constellation schemas (hybrid between the two) works and what the difference is. Additionally if only the fact table is normalized then one could use all 3 types of data warehousing models.

Fazer update no schema de um usuário

Olá! Recentemente no discord eu fiz um sistema de clans, usando o mongodb atlas, para aprender como ele funciona. O problema é que ele cria o schema no atlas certinho, mas dá o update no schema do usuário.

Aqui estão os dois Schemas:

const User = new mongoose.Schema({     _id: { type: String, required: true },     tag: { type: String, required: false },     clan: { type: Boolean, default: false },     clan_name: { type: String, default: 'The member has no clan.' },     coins: { type: Number, default: 0 },     wood: { type: Number, default: 0 },     rock: { type: Number, default: 0 },     gold: { type: Number, default: 0 }, });  const Clan = new mongoose.Schema({     _name: { type: String, required: true },     all: { type: Array, default: [] },     owner: { type: String, required: true },     co_owners: { type: Array, default: 'The clan doesn\'t have co-owners.' },     members: { type: Array, default: 'The clan doesn\'t have members.' },     bankLevel: { Type: Number, default: 0 },     bankGold: { Type: Number, default: 0 },     bankRock: { Type: Number, default: 0 },     bankWood: { type: Number, default: 0 },     members_counter: { type: Number, default: 0 } }); 

Este é o código do comando para criar o clan:

const { Command } = require('discord-akairo'); 

const { RedEmbed, GreenEmbed } = require(“../../Helpers/EmbedHelper”); const Database = require(“../../Database”);

class CreateClanCommand extends Command { constructor() { super(‘createClan’, { aliases: [‘create-clan’, ‘criarClan’, ‘criar-clan’], category: ‘RPG’, args: [{ id: ‘name’ }] }); } async exec(message, args) {

        await Database.Users.findOne({ '_id': message.author.id }, function (error, document) {          if (document) {             if (document.clan === true) {                 return new RedEmbed('já tens um clan');              } else {                 if (document.gold < 500) return new RedEmbed("n tens ouro", message);                 if (!args.name) return new RedEmbed("põe um nome", message);                 if (args.name.length > 15) return new RedEmbed("nome com + de 15 caractéres", message);                 if (args.name.length < 5) return new RedEmbed("nome com - de caractéres", message);                  let newClan = new Database.Clans({                     _name: args.name,                     all: [message.author.id],                     owner: message.author.id,                     co_owners: 'The clan doesn\'t have co-owners.',                     members: 'The clan doesn\'t have members.',                     bankLevel: 1,                     bankGold: 0,                     bankRock: 0,                     bankWood: 0,                     members_counter: 1                 });                 newClan.save();                 new RedEmbed('criaste um clan', message)                  let updateUser = new Database.Users({                     _id: message.author.id,                     tag: message.author.tag,                     clan: true,                     clan_name: 'The member has no clan.',                     coins: 0,                     wood: 0,                     rock: 0,                     gold: 0,                 });                 updateUser.save();             }         } else {             return new RedEmbed('n tens conta', message);         }     }); } 

}

module.exports = CreateClanCommand;

E o erro fala sobre uma key duplicado, e eu não faço ideia como o resolver: inserir a descrição da imagem aqui

How to make Drupal 8 aware of entity schema changes that are managed external to Drupal

Though it’s ideal to centralize entity and schema management together within Drupal, there are possible use cases where the two must be managed independently:

  1. Drupal entities that are based on tables in an external DB
  2. Manual schema updates for tables with exiting data that are considered unsafe from a cross-DB support perspective, but may otherwise be safe in controlled situations (e.g. increase varchar length on mySQL).

Our case is specific to #1. We have Drupal entities based off tables in an external DB (using base_table = "dbname.tablename" convention), and the related schema is managed exclusively by another platform. This means that when schema changes are deployed externally we need to update the relevant entity definitions and installed schema definitions in Drupal without triggering the actual schema updates themselves (e.g. no CREATE/DROP/ALTER TABLE... calls). The best way to tackle this problem has always been a bit unclear, and some of the new constraints introduced in 8.7.x seem to cloud the matter even further.

It seems that there are several Drupal-specific layers that need to be navigated here, including the entity definition, the entity storage definition, the last installed schema definition, the actual schema management API and various caches. The interplay between these layers had been difficult to pin down.

Possible Approaches

Some solutions proposed in forums involve manual manipulation of each API layer and often ad-hoc workarounds (direct updates to key_value DB records) that seem quite questionable. Examples are here, here and the “Updating an existing entity type” notes here. There also appears to be some discussion around overall limitations in field-specific schema updates here.

Some relief may come in the form of the EntityDefinitionUpdateManagerInterface which seems to serve as a central place to abstract these various layers away, but it’s not clear which methods operate purely on storage/schema definitions and not the actual DB schema itself. For example, I have had some success (re)using the ::installEntityType() method to deploy changes after externally updating the DB schema, updating the related Drupal entity definition and then running:

$  entity_type = \Drupal::entityTypeManager()->getDefinition('my_custom_entity_type'); $  entity_definition_update_manager = \Drupal::entityDefinitionUpdateManager(); $  entity_definition_update_manager->installEntityType($  entity_type); 

This seems to update the related entity/field storage definitions and the schema definitions and does not seem to alter the DB schema (I guess because the related table is detected as already existing). But given that this method is documented for once-off use (not ongoing re-install use) this feels like “off-label” use with unpredictable side-effects.

I also suppose that a custom entity storage definition could be justified to deal with all this, but I assume that would require re-defining or re-wiring quite a few dependencies (notably the low-level SQL management) that otherwise work well with the native entity storage.

Final Question

So what is the best practice way to make Drupal aware of externally managed schema updates that impact Drupal entities?

I have a feeling that fully understanding this also requires a good explanation of how APIs used to manage Entity/Field definitions/storage are independent (or not) from core schema management tools like the Schema API and hook_schema().

Database schema for a interactive novel

I’m trying to create an interactive story telegram bot on Node-red. To store the story I’m using SQLite

How it works: Bot shows you a piece of the story, then shows some answers (buttons) regarding what you are going to do next (e.g. Go forward! or Stay and fight!).

Depending on your answer, bot gives you the next piece of the story.

I’m not familiar with DB table relationships.

Currently my DB structure is as follows:

Table:

Users (userid PRIMARY KEY INTEGER, username TEXT, currentstoryposition INTEGER)

Table:

Storyphrases (phraseid PRIMARY KEY AUTOINCREMENT INTEGER, phrase TEXT, answers TEXT)

The Answers field contains IDs of possible answers in plain text. e.g. “2,4,6”

And table:

Answers (answerid PRIMARY KEY AUTOINCREMENT INTEGER, answer TEXT, nextstoryposition INTEGER)

I assume I could “connect” Storyphrases and Answers together using some DB relationships. However, I don’t know how.

I understand that storing answer IDs like “2,4,6” in plaint text is weird.

Can somebody advise regarding the best DB structure for interactive novels like that?

Thanks.