Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [mysql]

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

Filter by
Sorted by
Tagged with
1 vote
0 answers
31 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 ...
9 votes
2 answers
5k 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
134 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
44 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
84 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
81 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
139 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 ...
10 votes
2 answers
20k 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
29k 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 ...
0 votes
0 answers
27 views

Best way to see changes to non-monitored linked tables

I am hoping that I can get feedback on my solutions, and other ideas as well. We have a table in a database being monitored by a NodeJS app, using the MYSQL binary logs, E.G.: ORDERS: orderID, ...
23 votes
4 answers
10k 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
292 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 ...
1 vote
1 answer
89 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 = ...
4 votes
5 answers
661 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 ...
42 votes
2 answers
35k 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 ...
-2 votes
2 answers
93 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-...
77 votes
15 answers
29k 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
32 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
141 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
239 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
267 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
91 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
137 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
7k 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, ...
0 votes
0 answers
39 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 ...
-2 votes
2 answers
72 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 ...
2 votes
0 answers
40 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 ...
2 votes
2 answers
908 views

Chat part of application - using UDP or TCP?

I'm trying to build a section of my app where two users can message each other. I've read about TCP and UDP and it seems like TCP is more suited due to ordered packet delivery. However, TCP requires a ...
-1 votes
1 answer
347 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 ...
0 votes
2 answers
420 views

Application/Database design where two tables have both 1-to-many and 1-to-1 relationships

I have an application where the requirements are to show a list of (just for example) car manufacturers. When you click on one of the manufacturers, a list of car models show up. You can then click on ...
6 votes
5 answers
709 views

Should I use a relational datamodel or Cassandra with ColumnFamily based indexes?

Currently we are working on some storage problems for log data from various servers and communication message logs (HTTP(S), XMPP). There will be many write operations and for read operations we will ...
0 votes
1 answer
140 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 | |------------------| | ...
0 votes
1 answer
174 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 ...
0 votes
0 answers
54 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 ...
-2 votes
1 answer
110 views

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

I have a kubernetes 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 is: does ...
-1 votes
3 answers
75 views

Architecture to show the number of results within the filter

I am building a web application connected to MySQL database that is supposed to work like an e-shop. User should be able to filter products, but I want to display the number of results that would be ...
0 votes
2 answers
108 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 ...
-3 votes
2 answers
45 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 ...
1 vote
2 answers
147 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 ...
1 vote
0 answers
46 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 ...
17 votes
1 answer
5k 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 ...
-1 votes
1 answer
187 views

Database abstraction layer

I'm currently developing an SaaS application in PHP, with Laravel, using its own DB class. Let's pretend we've got 2 classes under 2 namespaces, plus Laravel's own DB - so Illuminate\Support\Facades\...
0 votes
3 answers
444 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 ...
1 vote
0 answers
142 views

Should I decouple MySQL database?

I have two sites, one is a subdomain of the other. They both do different things entirely with one unifying goal. My intent is to have the main site do a majority of the heavy lifting, and the ...
103 votes
3 answers
102k views

What's the difference between MariaDB and MySQL?

What's the difference between MariaDB and MySQL? I'm not very familiar with both. I'm primarily a front end developer for the most part. Are they syntactically similar? Where do these two query ...
0 votes
1 answer
226 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 ...
1 vote
2 answers
4k views

Is it okay to keep open the MySQL database connection open through out the Application or open and close connection query-wise?

I'm developing a REST API using Nodejs along with MySQL as the database.Is it okay to keep open the MySQL database connection open through out the Application or open and close connection query-wise ? ...
0 votes
0 answers
16 views

Drag and drop and handling huge amount of documents to update position

I'm having a series of data as documents in a document db (CouchDB) Sample of a document looks like below { _id: xxx _rev: xxx data: xxx position: 1 } These documents displays in a web front-end ...

1
2 3 4 5
11