Skip to main content

Questions tagged [mysql]

MySQL is an open-source, relational database management system.

Filter by
Sorted by
Tagged with
0 votes
3 answers
131 views

.Net 8 XUnit: How to mock MySql in a CQRS API using integration tests?

This is a rewrite of my own .Net 8 XUnit: Should my tests use real data or how to mock MySql with CQRS? in a much more clear way: The reason for rewriting my previous question is because in the ...
3 votes
2 answers
351 views

.Net 8 XUnit: Should my tests use real data or how to mock MySql with CQRS?

I'm creating tests for our .Net 8 API. We use MySql for data storage and CQRS pattern for queries. In my personal opinion, I shouldn't use the real database for testing, because you will use an ...
1 vote
2 answers
101 views

Linking records through multiple tables?

I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of ...
2 votes
1 answer
182 views

Designing a flexible and comprehensive restaurant menu database schema?

I am working on developing a MySQL database schema for a restaurant menu system inspired by platforms like DoorDash, UberEats, and Yelp. My aim is to design a schema that is flexible and can handle ...
0 votes
1 answer
164 views

Our php codes base has 6 different ways to do INSERT … ON DUPLICATE KEY UPDATE, how do I fix it?

Our php codes base has 6 different ways to do INSERT … ON DUPLICATE KEY UPDATE. It happened over years because the php framework is evolving and my team members come and go, although I won't say we ...
-1 votes
2 answers
104 views

Best Practices for Storing Operational Hours Extending Past Midnight in MySQL

I'm building a PHP application to manage business operating hours, including cases where the closing time extends past midnight. I'm considering various ways to store these hours in MySQL and need ...
3 votes
2 answers
214 views

Optimistic locking vs separate lock store for background workers

I need to compute the value of a row R on a table based on rows queried on another table B. I need to do it in a queue worker because the computation of the value can be slow. A message is queued ...
0 votes
2 answers
601 views

Database dictionary table versus code defined enum

For the following table structure, for the metric_id column, what are the advantages of having a JOIN dictionary table versus an ENUM dictionary defined in the code? Assuming the metrics dictionary is ...
50 votes
3 answers
40k views

What is the difference between btree and rtree indexing?

I've noticed on MySQLWorkbench that you can choose how to store your indexes before forward engineering your design. The storage types are: BTREE RTREE HASH Researching this, I found some ...
0 votes
2 answers
385 views

Is it reliable to process millions of records with INSERT INTO SELECT directly in the database?

I have several million booking rows in a table and would now like to save the totals of the booking amounts in another table depending on the customer, account no., product, etc. Should this ...
-2 votes
2 answers
198 views

Designing a database schema for a website related to my town's history

I am working on digitizing old photographs, articles, ephemera, etc. related to my town's history, and I've been tasked to put these scans I've made onto a website for the public to see. There are ...
2 votes
2 answers
161 views

Do we have 2 logical query processings, one with indexes and one without indexes?

In the book "Inside Microsoft® SQL Server® 2008: T-SQL Programming" the behaviour of a sql query is explained. The following picture is taken from the book. I have some questions about the ...
0 votes
5 answers
978 views

Is it reliable to compress database backups with git?

I'm working as an intern at a fund. I spent the last month building a website for internal use, and now I think it's a good time to set up a backup scheme for the MySQL database at its backend. Funny ...
0 votes
2 answers
164 views

How to handle time differences in MySQL

I'm facing a problem with handling different timezones in MySQL. I have looked into similar questions that were previously raised, but I couldn't find a proper answer. The issue I'm encountering is as ...
65 votes
5 answers
38k views

Is backing up a MySQL database in Git a good idea?

I am trying to improve the backup situation for my application. I have a Django application and MySQL database. I read an article suggesting backing up the database in Git. On the one hand I like it,...
4 votes
2 answers
8k views

Polling versus push notifications

I have a hybrid web app for android. The webpage or website the app shows is in asp.net. The javascript for the webpage running in the webview of the app is sending out an ajax request every 10 ...
3 votes
3 answers
189 views

How to reliably determine changes in a DB table since some sort of "checkpoint"?

My scenario is fairly typical, but I can't figure out a good solution. I have a DB table (in reality several unrelated tables, but let's focus on one) where there is a fair deal of activity going on. ...
3 votes
2 answers
1k views

Is it a good practice to have two unique IDs for an SQL-Database?

Currently I want to create a SQL database effectively and "logically". Lately I read a lot about the issues that it is (mostly) not a good idea to define primary keys that can be seen from &...
15 votes
9 answers
9k views

Can it be acceptable to construct SQL queries dynamically?

I know that as a general rule, you shouldn't construct SQL queries dynamically because of the possibility of SQL injection. However, it could come in quite handy to break this rule and define for ...
4 votes
3 answers
8k views

Is it conceivable to have millions of lists of data in memory in Python?

I have over the last 30 days been developing a Python application that utilizes a MySQL database of information (specifically about Norwegian addresses) to perform address validation and correction. ...
1 vote
2 answers
558 views

Is it a good idea to design a database based on the API response data types?

I am trying to design my database, where i have to save some response data given from an external api as a webhook. This api will return me as well “referenceId” as an identifier, which i will use ...
9 votes
4 answers
4k views

How to store a fixed length array in a database

I have a mysql database in which I have drafts, each of which contains exactly 24 players the order of which matters. I am conflicted between having a drafts table with 24 extra columns for each ...
-3 votes
2 answers
175 views

Does DigitalOcean offer enough resources to run a scalable MySQL cluster?

I have a Kubernetes instance from DigitalOcean that has 2 worker nodes and 1 load balancer. Now I want to create a MySQL managed database cluster for the app that will run on that Kubernetes. Question ...
5 votes
4 answers
1k views

My father wants to learn PHP-MySQL to port his application. What I should do to help? [closed]

My father is a doctor/physician. About 15 years ago he started writing an application to handle his patient's medical records in his clinic at home. The app has the ability to input patient's medical ...
2 votes
1 answer
2k views

Implementing transactional entity lockouts with Spring and JPA

Spring Boot/Java 8/MySQL here. I have a widgets table in my MySQL DB that is modeled by a JPA entity like so: @Entity @Table(name = "widgets") @Data public class Widget { @Column(name = ...
-2 votes
2 answers
157 views

How to filter and concatenate multiple sql files into one database [closed]

I have an issue where I have multiple databases from previous projects that I would like to combine into one large database. These databases are stored in .sql files. The issue is that I only need ...
-2 votes
2 answers
63 views

How to serve customized content to each user, based on targeting criteria?

In my PHP Laravel application, I want to display special offers to users, depending on the quality of their house(s). There are 4 Models: User, House, Offer, OfferTargeting. Each User can have many ...
1 vote
0 answers
116 views

Backend Design: System requires both ElasticSearch and MySQL. In which scenario we fetch data from MySQL or ElasticSearch?

So, one of the backend system, having 5 resource types (tables) and some foreign keys as well, requires full text support for two of the tables. Also, for some use-cases, we require inner-joins for ...
11 votes
2 answers
8k views

Is indexing foreign keys a good practice?

Looking at DB tables created by a different developer I have noticed that whenever a table had a forein_key_id field/column, it was always an INDEX/KEY. I am not sure if it was manually created, or ...
1 vote
0 answers
430 views

Design MySQL database to store user workout data for Workout App

I am building a fitness app and I'm trying to figure out the optimal way to store the data is a MySQL database. I would like to have specific programs (for example, 'get jacked'), I would like a users ...
-3 votes
1 answer
65 views

Server performance and CMS scalability [closed]

I have been given the job of a colleague who has resigned and I don't know where to start. I hope you can give me some hints: It is about a CMS made from scratch (Javascript/jQuery/PHP/MySQL) which ...
0 votes
2 answers
99 views

Transferring data from 1 web app into another offline

I have a web app that will be used locally on 2 different site. The program is a Vue web app made with node.js and express, using MySQL database. Due to limitation (no internet in site 1), there is no ...
-1 votes
1 answer
107 views

Commit instead of rollback? [closed]

Simple example (PSEUDO CODE): for (int i = 0; i < 100; i++) { START TRANSACTION; SELECT id, name FROM employees WHERE id = i; IF (someFunction(id)) { ROLLBACK; ...
-2 votes
2 answers
197 views

Avoid redundant field using JSON type in MySQL [closed]

I have experienc with designing relational dbs but first time attempting to store JSON in a mysql table. I am using mysql 5.7.8 which has the ability to store json data type as a column. When I create ...
0 votes
3 answers
1k views

How to generate URLs for a custom URL shortener?

Due to some issues with other shorteners like goo.gl (disabling my links for example) I want to create my own URL shortener. I am looking to have a single table that will contain the following columns ...
7 votes
4 answers
1k views

QR Codes as Booking Confirmations for Conference?

A client of mine is holding a conference and we have the task of creating a booking system for them. However they have requested that we use QR codes so that on the door, a person can simply present ...
12 votes
2 answers
22k views

Creating a separate table for images or adding "image fields" to many tables?

I need to create a database in which several tables have images. For example, users have profile pictures and uploaded ones and products have many pictures, as well. Is it better to have one SQL table ...
10 votes
7 answers
32k views

What is the best approach for database design with lots of columns?

I am writing a query based financial application. It lets the user to write complicated equations (much like WHERE part of an SQL query) and find companies matching those criteria. For the above, I ...
23 votes
4 answers
11k views

Store images in the database, or in files with a database link?

Is it appropriate to store the image files in the database? Or it would be better to store only the path of the file in the database, while keeping the file itself on the server? Are there any other ...
-1 votes
1 answer
2k views

Locking a DB table and waiting for writing/reading it from a web application

I'm working on a web application using Spring (Java and JPA + Hibernate) and I was wondering if there is a way of locking a MYSQL table and then when another web service (or even another thread from ...
4 votes
5 answers
2k views

How can you ensure order of execution in concurrent tasks?

Here is what I am specifically doing: I have a thread-safe queue One 'write' thread constantly writes to the queue with data that comes from another service Multiple 'read' threads take from the ...
-2 votes
2 answers
158 views

Database Structure for The Best Efficiency

I want a database table that can hold all of the group chat messages from all of the group chats. The max amount of users per group chat would be 20. The max amount of group chats per user would 25-...
78 votes
15 answers
30k views

Why should passwords be encrypted if they are being stored in a secure database?

I have a web service. Right now, I have passwords stored in plain text in a MySQL table on my server. I know this isn't the best practice, and that is why I am working on it. Why should passwords be ...
0 votes
1 answer
78 views

Database schema for two different types of elements on map

I wrote a software for a customer that shows clients on a map (Google Maps). I store the clients on a table on the MySQL database (table clients) and show them on the map using markers. Each clients ...
0 votes
1 answer
523 views

Database normalization for grade management system

I am working on a grade management system web app for a university and I came up with this database schema How the app work: In the user table I am going to keep all the users data and the ...
-4 votes
1 answer
332 views

Design a login system

I have a task of designing login system with the requirement below: If the user login over three times, lock account for the first 5 minutes. The next failed 3 times login-in will lock the user ...
2 votes
3 answers
301 views

Is it best practice to always try to return a minimum number of rows from the database?

First-time poster so please be gentle :) I have a rather large table in my database (MySQL in case it matters) that holds lat/lng values of certain points of interest around the world (table name: ...
0 votes
0 answers
141 views

Fill Docker Container from existing remote database

I do have n nodes managed by Kubernetes, all of them running several containers. A container may contain a database alongside with an application. As containers should be stateless, all data within ...
0 votes
1 answer
271 views

Variable WHERE clause while avoiding sql injection

I want the user to be able to provide a custom WHERE statement against a mysql-backed platform... do I need to worry about any possible SQL injection attacks notwithstanding invalid sql? See the ...
15 votes
6 answers
15k views

Should I use DATE or VARCHAR in storing dates in MySQL?

Simply put, I have date and time attributes on an Orders table. The data types for these attributes are MySQL's DATE attribute. But everytime I echo the date and time in PHP it just gives me a string, ...

1
2 3 4 5
11