Questions tagged [relational-database]
A relational database is a database consisting of relation variables (which are also called *relvars*, *R-tables* or just *tables*). The definition, manipulation and integrity rules of relational databases are based on relational operations equivalent to or similar to the Relational Algebra and Calculus. Relational database principles are the basis of a substantial part of data management theory and practice.
6,832
questions
1598
votes
8
answers
338k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
434
votes
4
answers
549k
views
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
- 23.8k
433
votes
8
answers
778k
views
What is the difference between Views and Materialized Views in Oracle?
What is the difference between Views and Materialized Views in Oracle?
- 81.1k
410
votes
7
answers
104k
views
When and why are database joins expensive?
I'm doing some research into databases and I'm looking at some limitations of relational DBs.
I'm getting that joins of large tables is very expensive, but I'm not completely sure why. What does the ...
- 28.8k
380
votes
2
answers
635k
views
Laravel - Eloquent "Has", "With", "WhereHas" - What do they mean?
I've found the concept and meaning behind these methods to be a little confusing, is it possible for somebody to explain to me what the difference between has and with is, in the context of an example ...
user4898812
275
votes
4
answers
238k
views
Does the join order matter in SQL?
Disregarding performance, will I get the same result from query A and B below? How about C and D?
----- Scenario 1:
-- A (left join)
select *
from a left join b
on <blahblah>
...
- 27.7k
214
votes
2
answers
188k
views
PostgreSQL Foreign Key syntax
I have 2 tables as you will see in my PosgreSQL code below. The first table students has 2 columns, one for student_name and the other student_id which is the Primary Key.
In my second table called ...
- 2,310
207
votes
9
answers
204k
views
Foreign Key to multiple tables
I've got 3 relevant tables in my database.
CREATE TABLE dbo.Group
(
ID int NOT NULL,
Name varchar(50) NOT NULL
)
CREATE TABLE dbo.User
(
ID int NOT NULL,
Name varchar(50) NOT NULL
)...
- 12.5k
197
votes
8
answers
71k
views
Storing time-series data, relational or non?
I am creating a system which polls devices for data on varying metrics such as CPU utilisation, disk utilisation, temperature etc. at (probably) 5 minute intervals using SNMP. The ultimate goal is to ...
- 19.8k
196
votes
5
answers
126k
views
Relational table naming convention [closed]
I'm starting a new project and would like to get my table and column names right. For example I've always used plural in table names but recently learned singular is correct.
So if I have a table user ...
- 3,332
188
votes
6
answers
216k
views
Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
I have read the quote :
data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF].
However, I am having trouble understanding 3.5NF or BCNF as it's called. Here is what I ...
- 5,526
180
votes
4
answers
69k
views
How to design a product table for many kinds of product where each product has many parameters
I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:
Support many kinds of products (TV, Phone, PC, ...). Each kind of ...
- 16.2k
166
votes
12
answers
51k
views
Using a Single Row configuration table in SQL Server database. Bad idea?
In developing a shopping cart application I've found that I needed to save settings and configurations based on the administrator's preferences and requirements. This information can be anything from ...
- 88.6k
166
votes
15
answers
306k
views
Difference between one-to-many and many-to-one relationship
What is the real difference between one-to-many and many-to-one relationship? It is only reversed, kind of?
I can't find any 'good-and-easy-to-understand' tutorial about this topic other than this ...
- 1,854
162
votes
9
answers
116k
views
NoSql vs Relational database [closed]
Recently NoSQL has gained immense popularity.
What are the advantages of NoSQL over traditional RDBMS?
- 84.8k
147
votes
8
answers
33k
views
Use cases for NoSQL [closed]
NoSQL has been getting a lot of attention in our industry recently. I'm really interested in what peoples thoughts are on the best use-cases for its use over relational database storage. What should ...
- 18.5k
129
votes
14
answers
51k
views
When I should use one to one relationship?
Sorry for that noob question but is there any real needs to use one-to-one relationship with tables in your database? You can implement all necessary fields inside one table. Even if data becomes very ...
- 2,608
128
votes
8
answers
78k
views
Comparison of Relational Databases and Graph Databases
Can someone explain to me the advantages and disadvantages for a relation database such as MySQL compared to a graph database such as Neo4j?
In SQL you have multiple tables with various ids linking ...
- 11k
126
votes
9
answers
197k
views
Why use multiple columns as primary keys (composite primary key)
This example is taken from w3schools.
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
...
- 2,343
119
votes
9
answers
78k
views
What are the uses for Cross Join?
A cross join performs a cartesian product on the tuples of the two sets.
SELECT *
FROM Table1
CROSS JOIN Table2
Which circumstances render such an SQL operation particularly useful?
- 6,076
115
votes
7
answers
162k
views
Difference between a theta join, equijoin and natural join
I'm having trouble understanding relational algebra when it comes to theta joins, equijoins and natural joins. Could someone please help me better understand it? If I use the = sign on a theta join is ...
- 7,983
115
votes
8
answers
348k
views
How to create relationships in MySQL
In class, we are all 'studying' databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using ...
- 14.4k
98
votes
26
answers
36k
views
How do you like your primary keys? [closed]
In a fairly animated discussion in my team I was made to think what most people like as primary keys. We had the following groups-
Int/ BigInt which autoincrement are good enough primary keys.
There ...
98
votes
1
answer
82k
views
Modeling Product Variants
I've been trying to model product variants and thought that I might need to use EAV. I might have been able to do it without EAV, but I'm concerned that I might have missed something. Here's my design:...
- 40.1k
85
votes
8
answers
107k
views
What is the difference between a Relational and Non-Relational Database?
MySQL, PostgreSQL and MS SQL Server are relational database systems, and NoSQL, MongoDB, etc. are non-relational DBMSs.
What are the differences between the two types of system?
- 33.5k
84
votes
8
answers
83k
views
Still Confused About Identifying vs. Non-Identifying Relationships
So, I've been reading up on identifying vs. non-identifying relationships in my database design, and a number of the answers on SO seem contradicting to me. Here are the two questions I am looking at:...
- 34.6k
83
votes
6
answers
86k
views
Composite Primary Keys : Good or Bad?
Although one can use composite primary keys, for the case below, is it really a bad practice? The consensus on Stackoveflow seems to go both ways on this issue.
Why?
I want to store payments for the ...
- 1,560
81
votes
6
answers
53k
views
Pros/cons of document-based databases vs. relational databases
I've been trying to see if I can accomplish some requirements with a document based database, in this case CouchDB. Two generic requirements:
CRUD of entities with some fields which have unique ...
- 7,912
73
votes
7
answers
24k
views
When shouldn't you use a relational database? [closed]
Apart from the google/bigtable scenario, when shouldn't you use a relational database? Why not, and what should you use? (did you learn 'the hard way'?)
72
votes
5
answers
49k
views
Database design for user settings
Which of the following options, if any, is considered best practice when designing a table used to store user settings?
(OPTION 1)
USER_SETTINGS
-Id
-Code (example "Email_LimitMax")
-Value (example ...
- 64.1k
70
votes
3
answers
51k
views
Practical example for each type of database (real cases) [closed]
There are several types of database for different purposes, however normally MySQL is used to everything, because is the most well know Database. Just to give an example in my company an application ...
- 11.8k
70
votes
4
answers
44k
views
How to persist a graph data structure in a relational database?
I've considered creating a Vertices table and an Edges table but would building graphs in memory and traversing sub-graphs require a large number of lookups? I'd like to avoid excessive database reads....
- 1,369
68
votes
10
answers
37k
views
How should international geographical addresses be stored in a relational database?
Given the task of storing international geographic addresses in a relational table, what is the most flexible schema? Should every part of the address be broken out into their own fields, or should it ...
- 98.7k
68
votes
5
answers
36k
views
Are relational databases a poor fit for Node.js?
Recently I've been playing around with Node.js a little bit. In my particular case I wound up using MongoDB, partly because it made sense for that project because it was very simple, and partly ...
- 9,332
67
votes
3
answers
98k
views
Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?
I want to start using table relations in a new project.
After some googling I got 2 tables set up as InnoDB:
The keys I want to link are
->users->userid (primary)
->sessions->userid (index)
The ...
- 1,110
66
votes
8
answers
111k
views
How can I find MAX with relational algebra?
Working with databases, how can I find MAX using relational algebra?
- 715
66
votes
3
answers
32k
views
How to organise a many to many relationship in MongoDB
I have two tables/collections; Users and Groups. A user can be a member of any number of groups and a user can also be an owner of any number of groups. In a relational database I'd probably have a ...
- 6,014
63
votes
5
answers
56k
views
Database design: 3 types of users, separate or one table? [closed]
I have 3 types of users:
Admins
Suppliers
Employees
Each user type will have different user interface and access different types of data. Their only similarity is they are using one web application ...
- 15.6k
63
votes
6
answers
95k
views
What is the difference between MySQL & MySQL2 considering NodeJS
I have read the following:
What the difference between mysql and mysql2 gem
So far I have only used MongoDB with NodeJS and I want to be able to learn MySQL for any of my relational database needs. ...
- 5,261
61
votes
2
answers
45k
views
Relational Data Model for Double-Entry Accounting
Assume there is a bank, a large shop, etc, that wants the accounting to be done correctly, for both internal accounts, and keeping track of customer accounts. Rather than implementing that which ...
- 1,032
56
votes
4
answers
53k
views
What are the known ways to store a tree structure in a relational DB? [closed]
There is the "put a FK to your parent" method, i.e. each records points to it's parent.
Which is a hard for read actions but very easy to maintain.
And then there is a "directory structure key" ...
- 53.1k
56
votes
6
answers
41k
views
When to use MongoDB [closed]
I'm writing an application that doesn't necessarily need scaling abilities as it won't be collecting large amounts data at the beginning. (However, if I'm lucky, I could down the road potentially.)
I ...
- 2,255
55
votes
11
answers
190k
views
What is the difference between a candidate key and a primary key?
Is it that a primary key is the selected candidate key chosen for a given table?
- 14.7k
50
votes
4
answers
221k
views
How to perform a LEFT JOIN in SQL Server between two SELECT statements?
I have two SELECT statements in SQL Server like these:
(SELECT [UserID] FROM [User])
(SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043)
I want to perform a LEFT JOIN between these two ...
- 4,086
49
votes
6
answers
32k
views
When to use a key-value data store vs. a more traditional relational DB? [closed]
When would one choose a key-value data store over a relational DB? What considerations go into deciding one or the other? When is mix of both the best route? Please provide examples if you can.
- 31.1k
46
votes
4
answers
21k
views
Triple Stores vs Relational Databases [closed]
I was wondering what are the advantages of using Triple Stores over a relational database?
- 1,489
45
votes
3
answers
116k
views
Relational vs. Dimensional Databases, what's the difference?
I'm trying to learn about OLAP and data warehousing, and I'm confused about the difference between relational and dimensional modeling. Is dimensional modeling basically relational modeling, but ...
- 30.3k
40
votes
5
answers
4k
views
Safe modelling of relational data in Haskell
I find it very common to want to model relational data in my functional programs. For example, when developing a web-site I may want to have the following data structure to store info about my users:
...
- 13.7k
39
votes
5
answers
28k
views
what are the advantages of defining a foreign key
What is the advantage of defining a foreign key when working with an MVC framework that handles the relation?
I'm using a relational database with a framework that allows model definitions with ...
- 35k
37
votes
1
answer
82k
views
What is the difference between 3NF and BCNF?
Can someone please explain the difference between 3NF and BCNF to me? It would be great if you could also provide some examples. Thanks.
- 766