Questions tagged [mysql]
MySQL is an open-source, relational database management system.
531 questions
3
votes
3
answers
235
views
Best Practices for Implementing a Heartbeat Feature in a Laravel App to Track Offline Status
I'm trying to implement a heartbeat feature for offline tracking that just sends an offline message to the server once the web browser app (Laravel-based) is offline. Ideally it will ping the app's ...
- 41
2
votes
3
answers
420
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g. name, email, phone_number, address, etc.)
Work-...
- 131
0
votes
3
answers
290
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 ...
- 223
3
votes
2
answers
690
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 ...
- 223
1
vote
2
answers
129
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 ...
- 159
2
votes
1
answer
924
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 ...
- 145
0
votes
1
answer
231
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 ...
- 3,305
-1
votes
2
answers
270
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 ...
- 145
1
vote
2
answers
786
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 ...
- 131
-2
votes
2
answers
271
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 ...
- 13
2
votes
2
answers
194
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 ...
- 29
0
votes
2
answers
224
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 ...
- 139
3
votes
3
answers
429
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. ...
- 5,440
4
votes
2
answers
2k
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 &...
- 75
15
votes
9
answers
11k
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 ...
- 185
1
vote
2
answers
760
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 ...
Lackeeee
9
votes
4
answers
5k
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 ...
- 101
-2
votes
2
answers
246
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 ...
- 7
-2
votes
2
answers
73
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 ...
- 101
1
vote
0
answers
137
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 ...
- 121
1
vote
0
answers
513
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 ...
- 121
-3
votes
1
answer
77
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 ...
- 97
0
votes
2
answers
121
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 ...
- 19
-1
votes
1
answer
120
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;
...
- 11
-2
votes
2
answers
233
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 ...
- 139
-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 ...
- 47
2
votes
1
answer
3k
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
190
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-...
- 39
0
votes
1
answer
100
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 ...
4
votes
5
answers
3k
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 ...
- 457
0
votes
1
answer
675
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 ...
- 21
-4
votes
1
answer
375
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 ...
- 101
2
votes
3
answers
340
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: ...
- 131
0
votes
0
answers
165
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 ...
- 101
0
votes
1
answer
358
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
16k
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, ...
- 215
0
votes
0
answers
72
views
What is best approach to designing a local-to-cloud cloud-to-local Infrastructure?
I plan to have a cloud web application(CWA) and several local web application(LWA), let's assume 3 LWAs and 1 CWA. CWA === LWA.
This web application would consist of a database server, file storage ...
- 109
2
votes
0
answers
64
views
Scaling Order Management System for ecommerce
Tech Stack: I am using MySQL 8 with InnoDB engine. Application is built using Java with Spring & Hibernate. Database is hosted in AWS RDS and web apps in EC2.
I have two primary tables for ...
- 259
-2
votes
2
answers
1k
views
What's better, using DB or Storage services to save videos?
I am creating a website that allows to teachers to uploading courses, what's better, to just use the DB or should I use storage services, and how could I know the size of the videos?
I am using AWS if ...
-1
votes
1
answer
1k
views
Best practice to flatten JSON object and store it database
I'm fetching data from Clockify, flattening the JSON response, and storing it in the database where each property on the API response should have its own table's field.
I have read this article about ...
- 113
0
votes
1
answer
371
views
Does it make sense to have redundant DB fields to improve query performance, programming speed and code readability?
I am a bit torn about the database design for a solution. I have the following structure: (only the relevant fields are marked with #)
+------------------+
| activities |
|------------------|
| ...
- 129
0
votes
1
answer
1k
views
Is deterministic SELECT possible without specifying an ORDER BY?
When we show a table with many thousands of records, we only show a small part of the total result set so as not to send too much data to the client at once. Clients can request more parts of the ...
- 1,792
0
votes
0
answers
188
views
DataBase Design for frequent delete operations
We have a use case wherein we want to store incoming data and invalidate all the previously existing data pertaining to one parent entity.
ie Parent p1 --> has List< child> old_data ( already ...
- 1
-3
votes
2
answers
197
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 ...
- 133
0
votes
2
answers
122
views
How can you avoid doing hundreds/thousands of queries when utilizing classes in PHP?
I have been dipping my toes into object orientated PHP programming and I'm finding it useful but I can't help but feel I'm missing something in regards to how to utilize it.
Normally my classes and ...
- 125
-3
votes
2
answers
74
views
Does it make sense for a large scale SaaS project to use a single all powerful SQL/RDBMS database?
Scalability seems to be a thing now.
I was in a heated argument with one of my developer who insist on using MySQL for a data intensive application we are building. MySql is easy, fits the purpose and ...
- 121
1
vote
2
answers
195
views
Appropriate database schema
I'm currently collecting the data about flat prices. I have size, price, location, price per m2 and date of retrieving the data about flat(sample date). I'm plannig create MySQL database filled with ...
- 121
1
vote
0
answers
62
views
Best way to integrate google signin in existing user registration system with PHP/MYSQL [Logic]
I want to integrate google signin, in our existing user registration system. But not sure which approach will be good in terms of data security + user experience (UX) + speed. Even if its a good idea ...
- 111
19
votes
1
answer
7k
views
Is it possible/advisable to combine unit testing and integration testing?
I've built a Python script that consists of about 20 functions that extract data from MySQL via .sql files (I'm currently trying to learn SQLAlchemy ORM), do something to it and then put it back in ...
- 321
0
votes
5
answers
1k
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 ...
- 579