Search Posts

Category: relational-database

Using unique strings as Primary Key

how to – Using unique strings as Primary Key My game designers are creating dialog and game content by using a full unique ID per item, e.g., for an NPC in Quest I, Task I, for dialog IDs 1 and 2, they’d write: Q01_T01_NPC01_01 Q01_T01_NPC01_02 I usually use autoincrementing IDs for setting up relationships, but thought using these unique string IDs might be easier since: If we remove a row, the IDs in the spreadsheet […]

How to organize the versioning of data in a relational database

how to – How to organize the versioning of data in a relational database Maybe someone already asked this question, I apologize if I repeat. I had the following task. There is a contact information table for users (IDCONTACT, NAME, EMAIL,PHONE, URL). There are also two other entities of the question (IDQUESTION, DATE, CONTENT, IDCONTACT) and the answer(IDANSWER, IDQUESTION, CONTEN,DATE, IDCONTACT) for example. Users have the opportunity to adjust their contact details. The question is […]

sql query takes ages to complete

how to – sql query takes ages to complete I have two relational tables of which i both need data. The first (Main) table contains about 90k rows. The secondary table contains about 200k plus rows. I need to add some data of the secondary table to the results i get from the first table currently i do this in two parts: First: i select the required rows from the Main table this goes super […]

Using local MySQL Database in JPA driven Java Application

how to – Using local MySQL Database in JPA driven Java Application I’m trying to create a Java application that will run on a hypothetical client machine, where members of staff can both view or add customer details from a local MySQL database. I’m trying to use JPA to do so, with query methods being in this form: public class DataManagerImpl implements DataManager{ @PersistenceContext private EntityManager em; public List<Customer> AllCustomers(){ TypedQuery<Customer> query = em.createNamedQuery(“Customer.findAll”, Customer.class); […]

Join Multiple Sql statements from the same table

how to – Join Multiple Sql statements from the same table I have read many post on this topic but I am not yet satisfied. I have a table called ticket with the following columns TicketID | AirlineID | PassengerID | TicketPrice | TicketVolume | DestinationCountry | ExitCountry | TicketDate` I have multiple queries like SELECT AVG(TicketPrice) FROM ticket WHERE TicketPrice between 552 and 1302 AND AirlineID=1 AND TicketDate between ‘2016-01-01’ and ‘2016-12-31’ GROUP BY […]

Optimizing Mysql role management query in web-application?

how to – Optimizing Mysql role management query in web-application? Let me start off by the structure of mysql Table structure for groups CREATE TABLE `groups` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `org_id` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `org_id` (`org_id`), CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`org_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Table structure for groups_perm CREATE TABLE `group_perm` […]

Has many trough with date verification in Laravel

how to – Has many trough with date verification in Laravel I need to refactor my data relationship since date featured included. I have three models: AppProduct; discounts()->hasManyThrough(‘AppDiscount’, ‘AppDiscountProduct’, ‘product_id’,’id’,’id’,’discount_id’); AppDiscountProduct; discount()->belongsTo(‘AppDiscount’, ‘discount_id’); AppDiscount; discountProduct()->hasMany(‘AppDiscountProduct’); discounts_product table: id, discount_id, product_id discounts table: id, name, start_date, end_date, etc. products table: id, name, price, etc. When I call $product->with(‘discounts’) the result is something like this {“id”: 1, “name”: “Wild Blue Shirt”, “discounts”: [{ “method”: “per_product”, “type”: “percentage”, […]

Database Schema : Multiple Admins (CMS)

I’m having a hard time to create a schema for my CMS. It is a reservation system for hotels/resorts. I have two users and two connected websites, one for the client and the other for hotel/resort owners (CMS). The owners should create his own account and using that account he can add more than one hotels/resorts, the problem is the owner should be able to add an admin or another user that can access his […]

How to store efficiently hierarchical data in PostgreSQL (LFT/RGT vs PARENT_ID)

We have a Java application and we want to create an abstract HierarchicalId class to use as an extend of any entity that is part of a tree. Our experience in MySQL showed us that it is much more efficient for READ operation to store the left and right position. See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for more details We will read 95% of the time, but the 5% remaining will still require CREATE/UPDATE/DELETE, we also need to be […]

SQL count one row if many exist

I have what I think is a pretty standard Postgres SQL problem. I can explain it, just can’t program it. I have 4 rows that have unique account IDs. Each of these rows shares the same longitude and latitude values, but their physical addresses differ (apartment 1A, apartment 2B…..). Each account has status of active or inactive. Each account either gets or does not get HBO. This data is all derived from a single table. […]

Next Page »