Questions tagged [relational-database]

A relational database is a digital database based on the relational model of data. This model organizes data into one or more tables (or "relations") of columns and rows

Filter by
Sorted by
Tagged with
13
votes
9answers
4k views

Should a surrogate key ever be exposed to a user?

Often in a table that has no natural key, it is still useful for users to be able to have a uniquely generated identifier. If the table has a surrogate primary key (and in such a case you would ...
52
votes
7answers
3k views

What happened to database constraints?

When I review database models for RDBMS, I'm usually surprised to find little to no constraints (aside PK/FK). For instance, percentage is often stored in a column of type int (while tinyint would be ...
14
votes
4answers
2k views

Does it ever make sense NOT to condense one to one relationships?

If we have Table A that has a one to one relationship with Table B, does it ever make sense to keep them apart? Or does it never hurt to combine them into a single table? Do either of these scenarios (...
8
votes
2answers
14k views

Database model for keeping track of likes/shares/comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts ...
6
votes
4answers
7k views

Is there really Object-relational impedance mismatch?

It is always stated that it is hard to store applications objects in relational databases - the object-relational impedance mismatch - and that is why Document databases are better. However, is there ...
15
votes
4answers
22k views

Is storing a list of strings in single database field a bad idea? Why?

Recently, I started to work on some legacy system. People that developed it, came to an idea to store list of strings in single field of database table. Lets say that it is an identifier for object ...
28
votes
4answers
8k views

Why many designs ignore normalization in RDBMS?

I got to see many designs that normalization wasn't the first consideration in decision making phase. In many cases those designs included more than 30 columns, and the main approach was "to put ...
16
votes
4answers
15k views

How should I represent an enumerated type in a relational database?

I am working on developing a relational database that tracks transactions that occur on a device I'm working on for my company. There are different types of transactions that could occur on the device,...
18
votes
4answers
3k views

How do I know my data is relational or object oriented in nature?

Just read these lines- If your data is object in nature, then use object stores ("NoSQL"). They'll be much faster than a relational database. If your data is relational in nature, the ...
17
votes
3answers
10k views

Document Database versus Relational Database : how to choose?

I'm a SQL guy, but I know there is Not Only SQL databases - document-database mostly. As with most technologies there are pro and cons for each technology. I've read some articles, but they were too ...
14
votes
6answers
2k views

In plain English, what is a database index most similar to? [duplicate]

Setup: Suppose you are teaching an introduction to Databases class, the students are CS students that have a working knowledge of tree structures, how they can speed up searches, and have probably ...
10
votes
2answers
7k views

B Tree compared to an R tree - Isn't it just a bunch of linked lists linked together?

I'm pretty familiar with a B Tree, mainly having to keep the databases well fed with electricity, air conditioning, and harddrive space. I associate with a double (doubl[ie,ey]?) linked list. Today, ...
6
votes
1answer
2k views

Elasticsearch and relational database combination

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), ...
6
votes
5answers
5k views

When keeping product price change history, what are the pros&cons of keeping track of price change events only?

When tracking something like pricing history, in my relational database, what is a recommended way to store pricing changes? For example I can: keep price change events only (aka a single date ...
5
votes
2answers
5k views

Would a NoSQL DB be more efficient than a relational DB for storing JSON objects?

I am using a library that returns a JSON object (as a string, but I think that's standard), and I would like to store the contents of this object into a local database. I'm fairly fresh-faced in ...
13
votes
4answers
958 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| |...
8
votes
5answers
4k views

Predicting advantages of database denormalization

I was always taught to strive for the highest Normal Form of database normalization, and we were taught Bernstein's Synthesis algorithm to achieve 3NF. This is all very well and it feels nice to ...
8
votes
4answers
13k views

Can anyone explain this one-to-one, one-to-many, many-to-one, many-to-many concept with respect to ORMs?

A lot of ORM sites and SQL tutorials mention these relationships as if they are obvious or to be taken for granted, but I don't fully understand why the distinctions need to be made. Consider two ...
7
votes
4answers
771 views

Database design for fields that should be NOT NULL eventually, but may not yet have a value

I have encountered this problem a number of times. Let's say you have a large object that you want to store in the database. Perhaps it's a dating profile for a user, and it has 100 fields. All of ...
4
votes
1answer
773 views

Suitable algorithm for joining records from multiple databases

I am in the process of writing a library that is used to be able to access data from a database. The library requires the initial definition of all the tables that the application will be using in the ...
4
votes
2answers
3k views

One wide table or multiple themed tables?

I'm trying to design a database for a simple text based game where the player characters have a large number of statistics that I want to track. Currently I have a few groups of related statistics, ...
4
votes
3answers
2k views

Designing a database with circular references for the-one-of-many case

Consider the following case: there are two models Image and User. Users have associated images and they can pick one of them as their profile image. There are essentially two ways of modeling this: 1)...
1
vote
1answer
850 views

Elasticsearch and PostgreSQL combination

I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL ...
1
vote
6answers
1k views

when should a database table be broken into multiple tables with relations?

I have an application that needs to store client data, and part of that is some data about their employer as well. Assuming that a client can only have one employer, and that the chance of people ...
5
votes
3answers
4k views

Is it okay to use a non-ID column for foreign key reference?

I have my tables as below Role(Role ID, Name, Other_Columns) Command(Command ID, Name, Other_Columns) I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName) ...
4
votes
1answer
1k views

How would one build a relational database on a key-value store, a-la Berkeley DB's SQL interface?

I've been checking out Berkeley DB and was impressed to find that it supported a SQL interface that is "nearly identical" to SQLite. http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbsqlbasics.html#...
3
votes
2answers
373 views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
2
votes
3answers
3k views

Database : Does it make sense to choose the zipcode as a primary key for an address?

I was following a tutorial on the normal forms of SQL databases, and I got confused landing on this example : https://www.tutorialspoint.com/sql/third-normal-form.htm. From CREATE TABLE CUSTOMERS( ...
2
votes
5answers
422 views

Compromise between formal correctness and convenience in database relations

Suppose that you are modeling and application in which you'll have 4 kinds of entities, say a Person a Job a Machine a Chemical Now you have to model all possible relations (N:M) between those ...
1
vote
1answer
70 views

Multiple intersection tables vs multiple joins

I have a hierarchical relationship between my tables, with the children having foreign keys referring back to their parent ids (assuming id is the primary key for each table): Department has many ...
1
vote
4answers
476 views

When denormalizing a database, do I need to keep the parent tables?

Say I have the following ER diagram: To implement the above ER diagram as a normalized database, I would create a School table, and a Student table, and a School_has_Student table (that only contains ...
0
votes
1answer
280 views

Handle database error for API server

Recently I worked with a Restful API server and encountered a problem with handling database error. The basic approach is every time a SQL exception occurred, the server will return "500" to client ...
-2
votes
1answer
171 views

Extensible hierarchical database design

This question is an extension to the question here (and follows the same hierarchy) - Multiple intersection tables vs multiple joins based on this answer - https://softwareengineering.stackexchange....