Questions tagged [mysql]

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

Filter by
Sorted by
Tagged with
3
votes
0answers
1k views

How to store unique sessions for logged in user(s) with different devices?

I want to do something like Facebook does. We can see any time where we're logged in. e.g.: Windows PC · New Delhi, India Edge · Active now Windows PC · New Delhi, India Chrome · 8 minutes ago ...
2
votes
2answers
80 views

Sql: Pre inserting relational entities vs the "lazy creation" of the entities

I have the following entities in my database (it's simplified): Student (id) StudentCourse (id, student_id, course_id) Course (id) Lesson (id) LessonCourse (id, lesson_id, course_id) ...
2
votes
2answers
6k views

Build a realtime chat app which stores messages in a MySQL database

I'm thinking of making a realtime chat app that would allow me to store user messages into a MySQL database. So far these are the two ideas I have. 1) Create and API which the message is sent to and ...
0
votes
2answers
2k views

Design - JSON Strings storing large amounts of data for large amount of users

I have a website that allows users to be "tracked" and track their time/work. This involves the users being able to update their account with my site at most every minute, but typically users do it ...
3
votes
1answer
621 views

Checking for duplicate with encrypted values

I was hoping to get some advice on a particular task I'm trying to implement. I have a table that stores secure data and returns an ID as a representation of that data. No problems there. So, for ...
2
votes
2answers
500 views

creating nodejs apps in php/mysql framework with later on full step by step migration to nodejs in mind

I'm looking for the most efficient path to convert over time my own made php framework for nodejs and I believe that nodejs experts and specific developers who had their years working with apache/php/...
2
votes
1answer
1k views

Storing logs on mysql database

I was looking for more information on logging user actions on the application (web based project). I did found this question, but it's a more conceptual answer, wich helped me getting started with ...
6
votes
5answers
2k views

Database vs Flat files for rarely accessed data

We are receiving realtime data from over a 1000 sensors, each of which sends a data point every 10 seconds in average, which is amounting to about 1 million rows of data every day. Our system gives ...
0
votes
1answer
311 views

MySQL linter: Letting an installed python package Integrate with a user-written python module

I'm working on a new style of database migration system that is coming along nicely. There is one last aspect of it that I want to finish, which is to enable MySQL linting. For background, this is a ...
-1
votes
1answer
101 views

Saving sets into database fields [duplicate]

I'm working in a project (with Python, SQL, Javascript) where the devlopers before me have started saving sets into database fields. Example Here the field "audience" can have sets as content. Is ...
3
votes
2answers
1k views

How to Define Service Area – with Zip Codes?

I'm working on a site for at-home services. What is the best practice to define a service provider's service area? My initial thought was to define each provider’s service area as a set of zip codes. ...
3
votes
1answer
2k views

Best way to query immutable MySQL Data

I have been increasingly using immutable data storing for my web-app, due to some advantages it has (simpler writes, ability to view history / activity). I am doing it using MySQL dabatases, simply ...
1
vote
1answer
1k views

Normalisation Vs Join , trade-off

In RDBMS, goals of Normalization: Free the database of modification anomalies Minimize re-design when extending Avoid bias toward any particular access pattern First step involves avoiding ...
-3
votes
2answers
156 views

How to deal with small frequent transactions

I have been given the task to create a system in which users can buy intellectual property (broadly categorized, hard to explain what they are really buying) from the system and can transfer funds ...
0
votes
1answer
176 views

Develop for MySQL and SQLite3 Concurrently

I am developing a javascript (Node.js) desktop program that works with an existing MySQL database. I would like to (eventually) distribute to others with based on SQLite, or MySQL if they need (or ...
0
votes
1answer
120 views

C# use same mysql databse with my website

The title pretty much says it all. I have a website hosted with ubuntu with mysql on it. Now I'm trying to figure out how can I use that same mysql with c# app. I have understood that mysql blocks ...
1
vote
1answer
462 views

Data modeling for NoSQL document database

Below are the common phases applied, for a real world requirement(data) map to DBMS specific schema. Conceptual models ER/EER/CODASYL/Hierarchical bridge the gap, in mapping the real world data(...
-1
votes
2answers
1k views

MySQL 5.7 json type or MongoDB document?

I'am working on a project that use MySQL as RDBMS. Recently, we have to manipulate a big json data. So, the first solution that comes in mind, and for many reasons, is to cooperate mongoDB with Mysql ...
2
votes
1answer
484 views

MySQL UUID storage/presentation

I'm looking at the way UUIDs are stored in MySQL. MySQL 8 still doesn't have a UUID data type, and it still doesn't have custom types, so there are two options - store it as a string or as a binary. ...
3
votes
2answers
599 views

Client or server check if insert or update in RESTful architecture

Before I began developing a RESTful API I used a query similar to this: $query = "INSERT INTO availability (user_id, date, status) " . "VALUES ('".$id."', '".$date."', '".$status."') " . ...
4
votes
3answers
6k views

Working with timezones when storing dates in mysql

I have just a basic site with registration. With all other data, it stores the registration date in MySQL. If I'm in USA or in Canada, and I register at 2017-03-07 (Y-d-m), in MySQL it's 2017-04-07 ...
1
vote
1answer
96 views

Handling multiple saved game data-instances in MySQL

I'm building my first ever web-based game in PHP/MySQL and I'am having difficulty conceptualising how to deal with saved game data instances. It's a football manager game with tables like... players, ...
1
vote
1answer
938 views

MySQL statements using MySqli and PHP7. How to migrate to prepared statements?

I am redesigning a PHP/MySql site that was about 7 years old and had lots of MySql statements. I had updated them all to use MySqli, but have recently heard about "prepared statements", so now I'm ...
0
votes
1answer
112 views

Importance of PHP unit tests regarding to basic classes

For 3 months, I've been working on a Web Project using PHP to create a platform to handle remote lights and sensors. I'm currently working almost alone on it, but we will be 2 on it in the next months....
56
votes
4answers
14k views

Why is using MySQL for a dictionary website a bad idea?

I'm planning to design and set up a database to store dictionary entries (usually single words) and their meaning in another language. So, for example, the table Glossary must have entry and ...
1
vote
2answers
1k views

SQL-Style name of variables in a stored procedure

As seen in the main question. It is not a syntactic problem that I have, or some error in the code. I would like to know what is the correct way to name the parameters variables in a stored procedure....
3
votes
1answer
5k views

How to store a matrix in a database

I am going to create a game that involves decision matrix's like the prisoner's dilemma. Much like the image below, except not necessarily symmetrical, and the values will change with each matrix I ...
-2
votes
1answer
94 views

Design implementation for a project

I have a question in regards to the implementation of a program that I am trying to do. I have a MySQL database with numerous users, and each have a task executed at a certain time. For each user ...
0
votes
1answer
2k views

What is a recommended way to store database information?

I am using a Java to connect to MySQL database. I want to iterate through this data and store it into a data structure. This data structure will be JSON.parse() and converted from server side to ...
0
votes
2answers
2k views

How to store large amount of session data

Current project that i'm working has a requirement to capture data relevant to a session and store. Purpose of this is to use these data for the analytics. Clients would use these data to generate ...
1
vote
0answers
63 views

Efficient structure for storing/handling lots of datasets

I am looking to develop a piece of software which will probably store hundred of thousands or even millions of datasets with 3 to 5 datasets to look up per ID. Is there any way to build something ...
2
votes
4answers
1k views

Using a relational and a non-relational database in the same project?

I ran into a problem on the project I'm working on right now. It is basically an app that stores a path based on GPS coordinates. The app runs on android an saves the GPS location every second and ...
1
vote
2answers
593 views

clarification about DAL and MySQLi functions in PHP

Up to now, I was considering MySQLi functions in PHP one example of DAL. And I initiated a simple project to publish in an article with the title "starting with PHP and DAL and MySQL" But when I made ...
1
vote
1answer
381 views

Handling Master/Slave on the Application Layer

So I am working on a legacy system, that had one database server. For reporting and to split the work load it has been moved to a Master / Slave setup. This process is done and seems to be working ...
4
votes
4answers
35k views

How to write a proper class to connect to database in Java

I'm currently writing an application and I'm struggling with the decision of how to correctly design a class to connect to a database. I came up with something like this: public class ...
0
votes
3answers
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 ...
6
votes
3answers
15k views

Transaction handling in DAO or Service layer in pure JDBC without frameworks

I have an application which works with pure JDBC. I have a dilemma where should transaction handling go, in Service or DAO layer. I have found that in most cases it should be implemented in Service ...
2
votes
1answer
1k views

What is an efficient design to store variables for different product lines in ER database?

This question is about refactoring existing database design. My data flow is User generates some data for product lines A, B, C Data is saved into the database once Data is later retrieved multiple ...
1
vote
4answers
509 views

How important is it to have multiple action per one page load?

In conventional web application there is single action per one button click from user. So clicking on submit form, send email or change status, delete user etc all this does single action per page ...
2
votes
2answers
116 views

Implementing count model in database

I've run into bit of an issue on implementing a count system in my project. I have a system where users can like a post. Should I keep a count of it in the same table like or should I remove the ...
1
vote
0answers
39 views

Cascade reference date through full query

I have created a complex query that references a specific date (year and month to report on) on numerous parts of the script. What would be the best artifact to have to only change 1 line of code and ...
1
vote
1answer
99 views

Sql server to PHP (api vs library)

Our agency is currently using and maintaining the CMS we have developed in .net MVC on SQL server. Since the cms is highly optimized and uses various cache tables to run very fast even with thousand ...
1
vote
1answer
633 views

Handle cron jobs for multiple timezone

How to handle cron jobs for multiple timezone? I am working on the PHP project that supports users from multiple timezones. I have used multiple cron jobs for multiple database operations. I am ...
3
votes
2answers
209 views

Storing user search terms

I have php application like airbnb. I want to store search values each time user search for listings. my number 1 solution would be storing it in mysql table, but I'm not sure its wise to do, if site ...
1
vote
1answer
1k views

How to implement ETL with MySQL?

I have a legacy MySQL Database (A), and a new reviewed structure for MySQL data base (B). Problem number one is that Database has to be alive and keeps receiving data from legacy apps. What I need is ...
5
votes
3answers
2k views

How to enforce the mandatory participation constraint at the many end in a one-to-many relationship?

Say I have the following ER diagram: Enforcing the mandatory participation constraint at the one end is easy, I simply make the foreign key (school_id) in Student NOT NULL. But how can I enforce the ...
0
votes
2answers
280 views

Should MySQL database tables only have a single unit of information per row?

I run an automated data collection service at work, which records measurements (temperatures, voltages, etc) from about a dozen sensors at a rate of about once a second. The MySQL database table looks ...
1
vote
1answer
880 views

Converting a large PHP codebase from mysql_ to PDO [closed]

At my workplace we're soon going to be tasked with removing SQL injection vulnerabilities from a large code base. The application was originally written around 8 years ago and after years of bolt-ons ...
5
votes
3answers
1k views

How would you go about making a search algorithm for a CRM?

I know this question is kind of broad, but all I really need is some best-practice code structure or a link to a good tutorial. I am working on a CRM that runs on php and mysql. Currently, our search ...
62
votes
11answers
14k views

Should I define the relations between tables in the database or just in code?

In my experience, many of the projects I have read in the past didn't have relationship definitions in the database, instead they only defined them in the source code. So I'm wondering what are the ...

1 2
3
4 5
11