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
167 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
1answer
115 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 ...
0
votes
1answer
39 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
374 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
2answers
24 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
0
votes
1answer
256 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
123 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 ...
0
votes
1answer
21 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
36 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
57 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
24 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
1answer
105 views

Converting a graph database schema to a relational database schema

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 ...
2
votes
2answers
484 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 ...
19
votes
5answers
5k 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?
0
votes
0answers
11 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
70 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
202 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
130 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
975 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
42 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
votes
1answer
93 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 ...
1
vote
1answer
836 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
189 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 ...
0
votes
1answer
89 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 ...
0
votes
1answer
18 views

Unable to create DB trace using DEA

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 ...
0
votes
1answer
55 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 ...
1
vote
2answers
50 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
29 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
42 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
31 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 ...
0
votes
1answer
50 views

Oracle Sequential or parallel read?

From web server i initiate two separate concurrent threads/requests to read from two different DBMS(oracle) tables(employee and department). Both web server and oracle are on separate node(quad core ...
26
votes
3answers
22k views

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
0
votes
1answer
79 views

Skip ERD in database implementation [closed]

AS we know that entity relation diagram(ERD) is an important concept in database implementation, But I was wondering that can we skip this step when creating a small database containing a few tables i....
1
vote
1answer
31 views

@OneToMany mapping but only one is needed

I have to create a new table to store version numbers of Terms&Conditions user have accepted and the time stamp of when they click accept. (This table only contains info related to user. Info ...
1
vote
1answer
37 views

How does this replication behaviour work in a RDBMS?

Consider a 3 node (N1, N2, N3) system N1 is master (used for writes) and N2, N3 (used for reads) are slaves Write request comes to update R to R1, N1 writes to disk and crashed without replication ...
1
vote
4answers
2k 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? ...
1
vote
1answer
158 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 ...
5
votes
1answer
389 views

Optimised Schema for City-to-City Distance Table

This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relationships for the data represented below. I want to scale it vertically (new rows), ...
3
votes
1answer
80 views

Plan for managing financial transactions in an easily and retrievable manner, indefinitely

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the rows as financial transactions. Clients need to be able to view these ...
0
votes
2answers
130 views

what is difference between RDBMS and NoSql on basis of how information is store in them?

I am searching web to understand the underlying concept of how actually information is stored in RDBMS and Nosql databases and found this: In the world of database technology, there are two main ...
1
vote
1answer
92 views

Is normalizing further to index on INT FK rather than VARCHAR worth it?

I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not ...
1
vote
0answers
39 views

Does two phase locking apply to individual transactions, or the schedule as a whole?

Problem I have conflicting perceptions of when a schedule violates 2PL or not. My first perception is that 2PL only applies to the level of a transaction at a time, since this is what I learned in a ...
4
votes
1answer
1k views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
1
vote
1answer
81 views

Organizing a comments table: How to justify the choice?

Business domain description Some of the entity types of interest with respect to the relevant business domain are money, user and progress. The users may need to post comments about money and ...
1
vote
0answers
35 views

Equivalent of stored procedures for other database systems?

I am trying to compare centralized, distributed database systems and blockchains in several points and got stuck when searching about 'stored procedures'. So far, I have thought that this is a ...
3
votes
1answer
201 views

Modelling the structure of a database involving products with multiple barcodes

What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 ...
1
vote
0answers
586 views

RDBMS sports league database

I am trying to create a database structure that will allow me to store records of league matches across multiple seasons and divisions. I have the following rules that need to be maintained: A league ...
2
votes
2answers
63 views

Should I repeat UNIQUE constraint on similar multi-index?

The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns. I have MySQL/MariaDB in mind but my questions certainly ...
0
votes
1answer
186 views

What is a staging table and how do you protect data loss when transferring the data?

What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with ...
-1
votes
1answer
57 views

Authorized, all privileged DB user and a DB instance with the same name - does it have a name? [closed]

Is there a term that encompasses an authorized, all-privileged DB user and a DB instance with the same name? Should I call this a DB stack / DB unit / DB packet / something else? Does this "stack" ...