Tagged Questions
4
votes
3answers
93 views
SQL design for representing a two-level hierarchy of objects with containment
I am trying to represent questions in quizzes.
The database is Mysql but I am using fairly generic sql. The assumption is that varchar cannot handle more than 255 though, so I am supporting longer ...
12
votes
3answers
175 views
Database schema for holding game save files
I have a database schema for a game to hold save files where some tables have a one-to-one relationship to classes. I was wondering if I should split up some tables.
Example: ...
5
votes
3answers
237 views
Extracting certain records
The purpose of this SQL query would be to extract the record 10192 only, because the end_time is both in the past and its foreign counterpart in the Node table ...
4
votes
3answers
146 views
Did you like my product?
I have the following MySQL query which selects product details and whether the viewing user likes them. I feel like it could be more efficient and that there is a more practical approach than using a ...
4
votes
3answers
82 views
Web application to insert into MySQL database
I'm currently learning PHP, SQL/MySQL and HTML to develop a web application project (I have very little practical experience in all of these.) To get started, I spent far too long and many questions ...
9
votes
1answer
72 views
Abusing Excel VBA… to maintain data stored in a database
I need to build a little UI to allow easy maintenance of some data stored in a MySQL database, and the end user wants to do that in Excel (I know, I know).
The code I came up with comes pretty close ...
6
votes
1answer
96 views
Music categorization database
Recently I've tried to teach myself basic things about MySQL databases, however I received some help on Stack Overflow with creating a good database structure trying to avoid redundancy as much as ...
11
votes
2answers
524 views
3
votes
3answers
74 views
Schema for allowing adding tags to entities in unrelated tables
I'm implementing a "tags" features to an already working solution.
Final users need to be able to add tags to three separate sections of the solution:
Posts
...
4
votes
1answer
658 views
Schema for Google Docs-like sharing
I want to allow users to share documents (and other stuff) in a database-driven application.
I have designed the following schema to allow for this (PostgreSQL). Some of the tables like ...
5
votes
1answer
44 views
Library database search with multiple JOINs is too slow
I have a MySQL query which runs very slowly. I've rewritten it many times but no improvements yet.
My current query for advanced search takes about 60 secs to complete. Any suggestions for improving ...
7
votes
3answers
153 views
Getting the top 10 stores within a category
I have the following query. I know there's an easier way to do this without the sub queries, or at least optimizing how they're used. I've read about cross joins and using ...
4
votes
4answers
497 views
Two while for the same query
If I have to loop results of a query echoing first all fields of a column, then echoing something not to loop, then fields of another column.
...
11
votes
2answers
78 views
Revision 1 - Step 1: PsychoProductions management tool project
I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold ...
5
votes
5answers
478 views
Username, Password and UserType Validation
I have two tables in the database:
Credentials (userid, password, usertype)
Customer ...
2
votes
1answer
17 views
Query the Max Nth per group
I'm trying to write the most efficient query I can to pull the relevant data I need for retrieving the latest conversations for a user, ordering groupings of conversations by most recent message in ...
7
votes
1answer
53 views
Step 1: PsychoProductions management tool project
I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold ...
2
votes
1answer
70 views
UserDAO with CRUD functionality for my UserRepository
This is my first attempt at creating a DAO.
I would like to get some feedback regarding the following aspects if possible:
Code readability
Efficiency
Usability
I also would appreciate any other ...
6
votes
4answers
258 views
Multiple SQL queries, one per ID
ids contains 100 ids that are comma delimited. Is it fine or does it need improvement?
...
10
votes
2answers
167 views
Complex query to count votes with a redistribution system
I've spent the last couple of evenings trying to build a system for redistribution of votes in a ranked voting system. I've finally come up with the model for redistributing surpluses from a candidate ...
4
votes
2answers
155 views
Injections and query
I made a class that connects to my DB and inserts some values. Is it secure or how can I protect this further from injections? The object declaration will come from variables with POST from a form, ...
5
votes
1answer
59 views
How can I optimize this SQL ranking generator query?
I have a World Cup fixture app where people can guess the results of the matches. Everything is working great, but each time I update the ranking, it takes 20 minutes for just 3000 users. So there ...
3
votes
2answers
89 views
Asking a user to take money from a checking account and move it to a savings account (Revised)
I made a few changes to the source code based on several suggestions I received yesterday. The changes I made were: improved the names of methods and variables to better describe what they do, passed ...
11
votes
7answers
1k views
Asking a user to take money from a checking account and move it to a savings account
I'm aiming to get this program right as I might show it at a job interview. The code below runs fine but I would like to know if there's anything that can be improved in terms of readability, good ...
6
votes
2answers
82 views
Modifying `sakila` database
This is not "real life" code. I'm trying to expand upon the well known Sakila sample database for MySQL to make it more complex. Step 7 (or 6) is running ...
2
votes
4answers
1k views
Multi column search
I have 4 text columns, a mix of Varchar and Text. I need to find rows in a table where all words searched for are present across the 4 columns.
The 4 columns are:
name
type
keywords
description
...
1
vote
1answer
43 views
First Schema for Location and Countries
This is my first MYSQL schema for caching location data (co-ordinates to place names) and referencing it with a country.
I was wondering what everyones feedback was on it, did I do a good job?
...
2
votes
1answer
55 views
Delivery geolocations query
I have the below MySQL query that does exactly what I need. But I am sure there are better queries that do the same thing.
If you see something very ugly or bad and have any suggestions, I will be ...
6
votes
1answer
131 views
Optimizing a query with many joins
I have a query which selects a show from anime_series, then selects corresponding information from other tables (such as studio, which is stored as a SMALLINT ...
5
votes
4answers
434 views
Database schema for Service Order Management project
I'm a student and I've created this MySQL database schema for my Service Order Management academic project. I want to know if I need to improve it and, if so, how I could do that.
...
2
votes
2answers
75 views
Nested SQL Statement
I haven't done too many nested statements in MySQL and I was hoping to have the below SQL looked at and let me know if there is a better/more efficient way of doing what I am trying to accomplish.
...
5
votes
2answers
59 views
Long SQL query with much duplication between two halves of a UNION
This works, but it's huge.
We need to repeat each table column on each UNION SELECT, as well as the WHERE clause for ...
5
votes
1answer
568 views
Logfile parser for Team Fortress Classic video game
I'm currently working on a logfile parser for a pretty old videogame called Team Fortress Classic, which can be compared to Counter-Strike 1.6 and is available on Valve's Steam platform.
I started ...
2
votes
2answers
63 views
Abusive use of long database query?
I somehow acquired the idea that I should reduce the number of trips to the database as much as possible and so I use a lot of very long queries.
For example, I had a function that does this:
...
-1
votes
1answer
96 views
Is this shopping site safe from SQL injection attacks? [closed]
Please verify security from SQL injection attacks.
homepage.php
...
7
votes
1answer
81 views
A faster way to combine two SQL table
I would like to give it a try and ask about my codes on here from now on to see if there are improvement for'em
this code is supposed to combine two sql tables into one.
...
7
votes
4answers
519 views
Bad practices in this procedure to calculate player statistics?
I recently self-taught myself MySQL as quickly as possible because it was needed for a project that I'm working on. Now that I've got some time I've been working on improving some thrown together SQL ...
2
votes
2answers
205 views
Speed up MySQL query with IN clause
I've tried to improve the speed of my query in MySQL, but when condition IN clause happens too often, the query runs very slowly. Can anyone help me optimize the ...
5
votes
2answers
53 views
SQL Query that pulls rows in current table based on parent id optimization
I am creating a user permission system. I will have my user permissions set up off of "objects" and not pages. For example, I have an EVENTS section. Within that ...
5
votes
1answer
99 views
Query performance of a SELECT statement using WHERE and NOT IN
Can anyone help improve the performance of my query? When I run it, it locks the database for 5 to 10 minutes.
...
6
votes
1answer
69 views
Removing image records if no physical file exists
I have a working script that selects image fields in all tables and empty their values if the physical file doesnt exist.
...
1
vote
1answer
70 views
Performance problem: Ranking with ties and skipping rows on passmark condition
I am using the following code for calculating ranking and it is working great for not large data. But when it comes to large data, processing time takes almost a minute. Please can anyone suggest a ...
5
votes
1answer
101 views
System for inputting and monitoring worker shifts
I've been building a system for inputting and monitoring shifts for casual staff, who work across multiple sites with the ability to generate accounting information.
I've had some help from Stack ...
2
votes
1answer
270 views
Optimize MySQL in a stored procedure
How should I optimize my code for better performance? When I execute the code outside of MySQL stored proc it is 500% faster.
MySQL stored procedure
...
3
votes
2answers
71 views
2
votes
3answers
463 views
choice of tables for a project to handle attendance
I'm working on my project in php [handling students attendance system]. I have a list of students along with their id[jntuno] and I need to create a database in mysql for storing the daily attendance ...
7
votes
2answers
148 views
Local user registration
More javascript (nodejs) to go with the passport wrapper I just posted:
UserBook.js
...
1
vote
2answers
845 views
SQL database design e-commerce
I am new to database design but am fairly familiar with SQL and its syntax. I want to create a database for an E-commerce website that will sell a single product type such as a shirt. They could be ...
2
votes
2answers
68 views
3
votes
3answers
122 views
Is this DB design normalized? [closed]
Is this normalized properly? Is there a more efficient way to design this DB?
A Profile can have unlimited albums, any user can create an album from anyone's videos. An album may contain many videos, ...
