Questions tagged [rdbms]

Relational Database Management System, a widely used type of database management system characterised by its extensive use of joining as a core operational principle.

Filter by
Sorted by
Tagged with
1
vote
1answer
171 views

MYSQL - How to optimize VIEW performance?

Total profiles - 100000 Each profile is connected to one or more platform Each profile have 50 Tags Each profile have 4 themes I have a user view (total users around 100000) where I am joining all ...
0
votes
1answer
22 views

MySQL write performance after truncate & optimize

Once a year we truncate a table of several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system. However, as this is write ...
0
votes
0answers
17 views

Relational DB which can do inserts at billions of rows with multiple indexed columns?

I am looking for advice on a database to potentially replace SQLite for my application. To briefly describe my DB requirements: needs to support billions of rows and the most queried table has ...
2
votes
1answer
80 views

Unable to create DB trace using DEA [closed]

I want to create a DB trace file using DEA(Database Experimentation Assistant). I am using below softwares: -SQL Server Express 2012 on Win10 machine where my DB is present. -DEA is installed on a ...
6
votes
0answers
51 views

Find friends of friends (recursively) efficiently using Postgresql

Objective: Users submit their Contact Books and then application looks for connections between users according to their Phone Number. Something like "6 Handshakes" idea (https://en.wikipedia....
0
votes
1answer
405 views

How to store external IDs from different sources?

I have a table of customers with a PK that we can use as an identifier for our internal system. These customers may belong to different companies, each of which may have an external ID in some ...
0
votes
1answer
190 views

How can Update table column with the virtual column?

I want to update repetitive columns, And I build virtual column for did this. I can "select" but can't "update" Here is SQL Fiddle : http://sqlfiddle.com/#!9/d09189/3 Also Code review : I have ...
0
votes
1answer
524 views

Speed up LEFT JOIN with OR operator in ON clause

I have a query that uses a LEFT JOIN with an OR operator within the ON clause. When commenting out the OR operator the query runs in ~150ms. With the OR operator kept in the query takes over 80s to ...
6
votes
3answers
2k views

Is a single-table relational database similar to a simple NoSQL database?

One advantage touted for NoSQL databases is the lack of a schema. In the case of a single-table relational database, there are no relationships between multiple tables, and new columns can be easily ...
-1
votes
3answers
34 views

Group by a col and concatenate two non group by col in oracel

I'm getting into SQL for the first time after 9 years of work only into Linux. Just during the course of practising sql i was trying to concatenate first_name and last name and get the avg salary of ...
3
votes
1answer
138 views

Converting a graph database schema to a relational database schema [closed]

I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL. My concern is that I have lots of relationships between entities (...
0
votes
1answer
42 views

Better way to ensure referential integrity of one column to a composite primary key?

Given a first table CREATE TABLE table1 ( column1 INTEGER, column2 TEXT, PRIMARY KEY (column1, column2)); does SQL have an idiomatic way to constrain column3 in a second table CREATE ...
1
vote
1answer
36 views

Is there a use case where RDBMS is the best data store for querying only? [closed]

I am trying to figure out whether there is any legitimate use case for Relational Data Store (like MySQL, PostgreSQL, etc) in DDD, Event sourcing and its projections. Hear me out. In event sourcing ...
0
votes
0answers
10 views

Hierarchical data with versioning

I have a domain where I deal with data which has a parent/child relationship, of arbitrary depth. Also, full time traversal needs to be enabled, to show the state of the data at a specific point in ...
0
votes
1answer
19 views

How to add/update data from slave server to master server in MariaDB?

I am experimenting on whether I can tweak the functionality of master-slave replication. I can write data in my master server which gets updated in the slave server but it cannot happen the other way. ...
0
votes
0answers
98 views

What is the difference between candidate key, super key and alternate key

I'm in IT but not in software development or database. I studied about DBMS subject 6-7 years back. I have confusion between these, candidate keys are like ideal candidates to become become primary ...
2
votes
4answers
3k views

Handling currencies in a database

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies? ...
-4
votes
1answer
46 views

test if the solution & SQL understanding is correct [closed]

Employee table- has several attributes such as EMP_ID, TITLE, DEPARTMENT, etc. a date column enabling us to know when was it updated. what does this query do? SELECT emp_id ,COUNT(*) as ...
0
votes
2answers
87 views

How to store daily records in SQL Server 2017 database? [closed]

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries. Example:...
0
votes
1answer
66 views

in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

i was wondering how a sql update works in two scenarios: table with 3 columns: a, b, c say the data value for a has to be changed. 1: update on a row setting all columns a, b, c (where b and c have ...
1
vote
1answer
247 views

Using RDBMS with pseudo foreign keys VS using NoSQL solutions

According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this ...
26
votes
2answers
16k views

Using MongoDB and PostgreSQL together

My current project is essentially a run of the mill document management system. That said, there are some wrinkles (surprise, surprise). While some of the wrinkles are fairly specific to the project,...
-2
votes
1answer
123 views

Oracle dataguard - Redo apply

In a low bandwidth environment when a transaction commits that generates a large amount of redo data will cause other independent transactions to slow down. This is because the redo data of small ...
2
votes
2answers
739 views

Repeatable Read: Which one is correct?

Which one is correct? With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 1. With transaction 1 as Repeatable Read, other ...
0
votes
1answer
338 views

What is the point of a consistent and partition tolerant multi-master DB Architecture as opposed to Master-Slave

I should emphasize that my question is in regard to CP (Consistent and Partition Tolerant) systems. A lot of widely used NoSQL databases such as Mongo and Redis seem to fall in the CP category. And ...
3
votes
1answer
6k views

MySQL: What is SQL mode and why we use it

If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen? Will my system use MySQL 4.0 or MySQL 5.1 ...
0
votes
0answers
15 views

Custom methods in MySQL / MariaDB ORDBMS

According to the book I am reading ORDBMSs come with an option to write a program (method) in Java (or other programming language), compile it into Java byte code and register via some SQL command. ...
1
vote
1answer
52 views

Physical co-clustering of two or more logical relations/tables in modern RDBMS

One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join ...
0
votes
2answers
67 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
1answer
40 views

Schema for 1 User to 0..N Apps?

I'm trying to design a schema for a user having multiple accounts. Example: User ID 1 has Apps A and B User ID 2 has App B only More App's will be created, i.e. C, D, etc., in the future. My first ...
5
votes
1answer
1k views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

As a follow-up to this question, I have one of my own. The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
0
votes
1answer
95 views

MySQL block read time?

My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory....
0
votes
1answer
41 views

Where MySQL is taking time either in disk read or computation? [closed]

From SQl case study System configuration is Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G) Query is select ...
0
votes
1answer
60 views

Big data table processing with RDBMS like MySQL?

I have two tables Customers and Orders and each has 100 million records. Now I have to join these two tables on the column customer_id in both Customer and Order table to generate a report. ...
0
votes
1answer
29 views

Disk block per table?

At T1, insert comes for table_1, goes to disk block_1. At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ? At T3, ...
2
votes
2answers
903 views

How we can do full text search and facets stuffs on BigQuery?

In the future, we will have millions ofs record in our web product. So we had used BigQuery for data storage and analysis. We have to build filters on our search page like Flipkart and Amazon provide ...
20
votes
5answers
6k views

Is ROLLBACK a fast operation?

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
1
vote
0answers
46 views

Understanding connection between different database concurrency control protocols

I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.): I don't know ...
1
vote
1answer
92 views

Schema design for products with redundant values

so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the ...
0
votes
1answer
349 views

Postgresql install extension

I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8. I would like to use the HIPOPG extension 1, 2 ...
5
votes
2answers
253 views

Does the concept of candidate key exist only in theory?

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
3
votes
1answer
1k views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self-referencing rows in a recursive relation table (a table contains foreign key points itself)? mysql> SELECT * FROM Employee; +-----+------+-------------+-----...
1
vote
1answer
92 views

Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, ...
1
vote
1answer
1k views

How to represent the following ternary relationship

The following image shows that each department can be associated with several employees and locations, and each location can be associated with several departments and employees; however, each ...
0
votes
1answer
628 views

Designing MySQL Database For Two Different User Types (Staff and Client)?

I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page. Both Staff and Client share the following ...
1
vote
1answer
397 views

MySQL schema design - how to decide between an `enum` vs. a table join

In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as: Stackoverflow post on efficiency of ...
1
vote
2answers
62 views

How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow? [closed]

In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier; https://aws.amazon.com/ec2/instance-types/t2/ It has: 20 ...
0
votes
1answer
32 views

Predefined rows conflicting with user entered rows

Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them? I could want to distribute an application with a table called ...
1
vote
0answers
90 views

Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has ...
0
votes
1answer
36 views

efficient primary key that does not expose business internals

I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks. Keys are short, efficient, but are auto incremented in a ...