How to model database for system with historic relationships (many-to-many and one-to-many)

I’m working on a system with a main entity, let’s say “Members”, these members have relationships to other entities and it’s important to keep the history to be able to query and know the relationships at a given time in history.

We mainly have two kind of relationships:

  • Company (most exist and can only be on company at a given time)
  • Tags (optional, multiple tags at any given time)

After looking at this I was thinking that I could model the relationships with dates on the “relation table” something like this:

Tag:
- Id
- Text

Company
- Id 
- Name

Member:
- Id
- Name

Member2Tag
- Id
- MemberId
- TagId
- Added (DateTime)
- Removed (Nullable DateTime)

Member2Company
- Id
- MemberId
- CompanyId
- Added (DateTime)
- Removed (Nullable DateTime)

This way I’ve managed to query for relationships at any given time in history, but I started thinking about this and felt a little unsure if the “Removed”-column is really needed, I guess it is since I can’t remove the relation-row (need to keep history).

I just wanted some feedback on the design, what do you think? Have someone else faced a similar problem and used another solution?

All the best!

Go to Source
Author: Markus Knappen Johansson

Tool for goods overview for employees

I’m looking for a tool that will help me create a very simple editable sort of (web)app-thing, which will work as shown on this image. I don’t know how to call such a thing, hence I have to describe it.

Basically the first screen shows 5-10 small icons-windows (A-J), all of them are hypertext links. When you click one of them (B in the image), you are taken to a different screen which could contain either another sort of junction like the first screen (B1-B20), or it could contain some final screen with desired information.

The idea is, that the company makes goods and ships them to multiple countries, but the goods and packing directions differ. The created app would allow employees to easily find what goods ship to which country and how they should be packaged.

So the first screen would contain country flags/names (A-J). After clicking on a country, you would be either taken to:

  • or a screen with goods and their packaging info for that specific country
  • a screen with said countrys regions (B1-B20) if goods and packaging differ by region and those region windows/icons would then take you to the final screen mentioned above

I know this is easily created in powerpoint with hypertext links, but this solution is not ideal, as it’s not that easily accessible at anytime from anywhere. A web app seems ideal.

I would be able to create this with my basic HTML/CSS/JS knowledge, but my skills aren’t good enough to create an administration for this app and the company would be dependant on me for editing the information, which isn’t good. It would be best if a manager could easily edit the information.

Go to Source
Author: Welsy

How to migrate data from custom site to WordPress?

Currently I have a site with around 6.000 articles running on a custom built cms platform, but due to demand from the client, we have been tasked to convert all data to WordPress. Everything is stored in MongoDB in a custom structure (nothing extraordinary) and I have been looking at how I could convert the data to match the XML structure of WordPress for easy import, though not sure if I have to build such conversion script myself, or if an open source data-pairing tool exists, where I can match a MongoDB query and its data to the designated fields in a WordPress DB.

The article contents have been structured using EditorJS which acts a lot in the same way as the Gutenberg editor in WordPress, though I would need to know, how I should convert the data to match the Gutenberg editor, or if I should simply render the data as HTML and save it as such in the XML for import?

Categories are stored in a separate collection with a parent -< child structure, and pages pretty much acts as normal html.

Q: How would one go about converting this to WordPress data?

Go to Source
Author: Dimser

Insert blob data with wpdb

I have a shortcode in my WordPress page with which I access the WordPress database with wpdb.

It works perfectly with wpdb-> get_results () and wpdb-> query (), but I am not able to execute an insert with a blob data.

With wpdb:: insert I can’t as it only accepts a string, int and float. With wpdb-> query () it doesn’t work for me either (I even tried with fbsql_read_blob ())

I can insert using the PDO class, but I don’t know if it can be done with any WordPress function. I understand that it is safer and will work better.

Thanks in advance.

Go to Source
Author: alvaroqm

Multiple intersection tables vs multiple joins

I have a hierarchical relationship between my tables, with the children having foreign keys referring back to their parent ids (assuming id is the primary key for each table):

Department has many Category Groups
Category Group has many Category(-ies)
Category has many Sub-Category(-ies)
Sub-Category has many Attributes.

Now, all these entities except for Attributes are optional meaning if I don’t select anything on my hierarchical cascading dropdown based UI, I need to display the Attributes that belong to all Departments, if I only select a Department then I need to display Attributes that belong to all Category Groups belonging to that Department and so on.

Obviously, one option to implement it is to do a inner join between all the tables to get to Attribute. For instance, if nothing is selected it will be:
Department inner join Category Group
inner join Category
inner join Sub-Category
inner join Attribute
to show all the attributes belonging to all departments.

The other thought in my head is to have intersection/relation mapping table(s) –
DepartmentAttributeRelation which has foreign keys to Department and Attribute,
CategoryGroupAttributeRelation which has foreign keys to CategoryGroup and Attribute and so on.
This will enable direct search to get to the Attributes given any entity.

My question is – Are there any downsides to the second approach above or are there any better approaches to solve this?

Go to Source
Author: linuxNoob

How to actually set up MariaDB replication/high availability

I’m setting up a high availability LAMP stack – so far, I have two servers with HAProxy running on both their frontends that own a floating IP. HAProxy redirects requests to backend Apache servers running on each of them (same machine, port 8000).

This all works, and I’m happy with this so far for high availability. I have yet to deal with the databases yet however.

Since I only have two servers, I don’t think I can do master-master like Galera cluster due to the requirement of quorum. So, I planned to do master-slave read only replication.

Most of the guides I see online don’t mention the MaxScale proxy that MariaDB suggests in its high availability guide (page 4 for example)

I’m just really confused overall on what to do and how to set it up?

From my perspective, I’d like to set up a MaxScale proxy on one of my servers and run MariaDB on both servers; MaxScale will handle things like redirects. How can I actually accomplish what I’m looking for?

Go to Source
Author: forkwasher

How Do I See the List of Users Allowed on a MS SQL Server Database

I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?

What are the ways to go about doing this? Is there an app or through a SQL query?

ANSWER

Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!

If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.

This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Happy user hunting! =)

Clear Terms from Taxonomy for Specific Post IDs?

Trying figure out how I can use some SQL/$wpdb->query to delete all the terms from a taxonomy that are related to specific post IDs. The following works beautifully to delete all the terms from a specific taxonomy, but I need to modify it to only remove ones associated with specific post IDs as mentioned.

// Clear Terms from Taxonomy 'post_tag'
DELETE t, tr, tt
FROM wp_terms t  
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

I’m also using the following to delete posts (works great):

$wpdb->query( "DELETE FROM $wpdb->posts WHERE ID IN (".implode( ", ", $postIds ).")" );

I’ve tried to do the following but it doesn’t clear the associated terms they just remain as orphaned in the DB, it just clears the relationship.

// Delete Post Terms
$wpdb->query( "DELETE FROM wp_term_relationships WHERE object_id IN (".implode( ", ", $postIds ).")" );

I’m a novice when it comes to SQL, any help is appreciated, thanks in advance!

Go to Source
Author: contempoinc

Is there a program that emulate folder/file structure for databases in ubuntu?

I have a database that has about 500,000 files. They’re text files, and the folder structure divides them into groups of about 40 files each. I chose this structure because I wanted to be able to use a text editor to view each of the files. But I’d like to reduce the number of files I have, so backups go faster.

I’m looking for a program (or a database structure) that emulates a standard file structure, but is actually one file. My ideal structure would be to have something like a few .zip folders, but allow the files and folders in the .zip to be read, accessed, and written by file references from python and and a text editor (e.g., with a simple reference like “nano dataGroup1.zip/folderA/file.txt”).

Is there such a program?

Go to Source
Author: quark

Architecture of optimized sync of JSON data to a (cloud) server

I’m building an application that generates JSON data based on the user’s notes. The notes are rich data that contain task lists and other types of elements inside it, so I use JSON format to store it. It currently saves this data to the browser’s local storage using JSON.stringify.

Now I’m building a feature that saves this data to a cloud server, but since the nature of the notes is that they constantly change, sending a big stringified object to the server at every note update (even if it has a 5-second debounce) seems a bit crazy to me. Depending on the size of the note, I’d have some 300kb calls or more to the server for a simple typo correction. Not good.

So I’ve looked into some services that support rich data JSON storage, such as Google’s Cloud Firestore or Amazon’s DynamoDB. Both look promising, but I’m still trying to wrap my head around my architecture for this. Here’s my plan so far:

  1. I’ll read the JSON from the server and keep a local object.
  2. At every local change, I’ll analyze the difference down the object tree and make some sort of diff string
  3. I’ll send only this diff string so the server can analyze and update its record.

Well, that seems to work. I would send a few bytes requests to the server, and not 300kb. It would require some code to analyze and generate the diff, but ok so far.

Now the server part is where my challenge is. Both Cloud Firestore and DynamoDB have rich data change functions, but it seems that for both, these operations must be pre-established according to the data itself, in other words, you must know the dataset in advance in order to make operations on it. Since my notes’ JSON are a dynamic dataset, I have no way of writing custom updates and inserts on it, it must be dynamically calculated via some sort of diff mechanism.

My questions are:

  1. Is this a good way to approach this problem or is there something I could be doing that would make things better/easier?
  2. Are there any cloud services, NoSQL databases, query languages, or libraries that would allow for this type of optimized dynamic JSON document update architecture?
  3. Would this be a good use case for GraphQL?

Go to Source
Author: raphadko

Which key-value database should I use and why?

Which key-value database should I use and why?

I have an application that scrapes webpages. Once a webpage is scraped, it has a unique identifier which is a string and it has the associated content which is also a string. I need to persist the webpage content, so that the next time I scrape the data for the same webpage I need to know whether the webpage has changed or not.

So either I might be storing the page identifier mapped with its checksum or simply its content. Anyhow, the basic need of storage is simple – a string to string map.

I think it is a use case for a key-value database. Since, my needs are very simple I don’t need a database full of fancy features. What could be a good open source key-value database that suits my simple needs?

Also, is it even necessary to use a database? I can fall back to a file storing the key-value. Will I lose performance if I use a database over a file?

Go to Source
Author: Navjot Singh