The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
1
vote
4answers
260 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 ...
-1
votes
1answer
55 views

Databse model: Entity that can have values from two different tables or user input values

I'm modeling a database, where I have a table called "appointments" (citas in spanish, but I would translate the tables's names for better understanding) where the user can create an appointment to ...
4
votes
1answer
168 views

What tactics to use to load the referenced data in repository implementation?

Say that I have a repository that is used as READ-ONLY data provides (never for the writes/updates) - the query part of the CQS, NO event sourcing, hitting relational DB. One of the essential rules - ...
-1
votes
3answers
123 views

DB architecture for managing items of different categories

I'm making a rental app which has many categories with unique properties Example: Category 1 Car which has Fuel, model, color, speed Category 2 Apartment which has Rooms, lift, ...
2
votes
2answers
936 views

How to design a relational database where users can create custom fields

Ive been looking around for a way to design a database for large amounts of leads (users) with custom fields. Ive check this out (How would you design a user database with custom fields), but this ...
3
votes
2answers
183 views

Is using -1 as a special Id a good idea in SQL?

I'm trying to determine if using '-1' in an Identity column has any drawbacks, or if there is a better way to do what I want. In my database I have User table and a Group table. All users must belong ...
2
votes
1answer
537 views

How can I turn my CRUD database application into a REST application?

I may have gotten myself confused, when reading CRUD vs REST and how they are similar, so I wanted to explicitly ask: can I turn my CRUD application into a REST application, when I have a specific ...
3
votes
4answers
485 views

DB Enumerations vs App Enumerations vs Lookup tables

We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices. Have the enumerations as ...
1
vote
3answers
63 views

Is this implementation for the “one” side of the relationship correct?

Say I have the following ER diagram: The "one" side of the relationship of the above diagram means the following: Each student can meet 0 or 1 teacher. So I can't do something like this: But can I ...
0
votes
3answers
178 views

Does the “one” side of the relationship also apply to the same record?

Say I have the following ER diagram: What I know is that the "one" side of the relationship means the following: Each student can meet 0 or 1 teacher. So for example student3 can meet teacher7, but ...
2
votes
1answer
335 views

Web application with relational database - best practice?

I am currently designing a web application which is pretty similar to Twitter but with some unique features for my company. Technologies are Vue2, Spark framework and PostgreSQL. I am trying to ...
6
votes
3answers
224 views

How to solve form submission consistency in a web application?

Imagine that you have a form with which you can edit some data, let's call it a Person form. Let's imagine that a user opens up this Person form, receives the current data that is stored in the ...
-2
votes
1answer
236 views

Relational Database Design with a table having redundant information

I am creating a database table that will store a single number 1 thru 16 in 2 fields (there will be many rows of data). I'm trying to avoid duplicating a lot of data and make my dbase most ...
1
vote
1answer
157 views

Which datastructure to use for temporary storage of intermittent and asynchronous sensor data?

Here an outline of the setup: Input data: I have several sources for sensor input data (GPS, and a bunch of other sensor data from different sources). All of those individual inputs consist of a ...
4
votes
2answers
169 views

Is it okay to structure a non-normal table in relational database to achieve a fast query?

Let's say we've got 3 entities here, User, Tag, Track and a single associative entity called Tagged. I need to count a tag frequency of a track and the common way is to count the record of tagged, ...
1
vote
0answers
181 views

How to maintain table relationship across services in with a micro-service architecture

I have a scraper service that looks for media online. For every source type, there are two tables. A common source table and a source_type_media table that are related by source.id to ...
0
votes
1answer
35 views

Extending photo album app with videos

I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension. The current design ...
0
votes
5answers
306 views

How to create a db schema for “if this then… elseif … elseif …” and so on?

I want to create an app where a user will go through a series of steps every day. All the subsequent step will depend on the previous ones, that is, 2 users can have different experiences depending on ...
8
votes
2answers
8k views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my projects and I never felt the need. I did work with professional databases that had foreign keys constraints. ...
2
votes
3answers
348 views

Database design for generic behaviour

I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something. Initially, I had thought that to make it possible to ...
1
vote
1answer
74 views

What are good ways to encode order between records of a relational table

For example, when an issue tracker offers the functionality to users to keep a prioritized backlog of tasks and used a relational database for persisting this information, what are effective ways to ...
0
votes
0answers
623 views

Use of UUIDs (Primary key) in a REST API

I faced the problem bellow when I designed our new app. We are thinking to have one extra column for some tables which is a UUID for security purposes. So, there are two different solutions. First ...
2
votes
3answers
284 views

Deal with Enumerations (Lookup tables)

I am trying to design the DB schema of my new project and I need some suggestions. I decided to create a lookup table in order to save the enumeration values. So I have a table Status with these ...
6
votes
5answers
3k views

Use UUID on top of auto increment ID

I am trying to design the DB schema of my new project and I need some suggestions. I am thinking to have one extra column for some tables which is a UUID. I wanted mainly for tables which contain data ...
1
vote
2answers
91 views

How to model a status log that doesn't allow duplicate statuses sequentially

I need to model the status of an object over time. I'm looking for the best way to model this so that concurrent database connections can look at the current status and update the status if the newer ...
0
votes
1answer
99 views

How to make an server side application independent of database

We have a server-side application (in JAVA, communicates via web services) that accesses a database. If we wish to sell just the application to a customer, so that he can use his own choice of ...
1
vote
2answers
99 views

Using Autoincrement Primary keys as Foreign Keys vs. unique codes

i’d like to push discussion on the same named thread again. I also use auto increment primary keys as foreign keys - mostly for code tables. Every row also has a code for identifying entries for ...
0
votes
3answers
179 views

How to associate one type with another in multiple contexts?

An application has types Game (and other business units) and Message. (Message is not a simple class with text, but a composition of a variable number of blocks like texts, images, and videos). ...
3
votes
2answers
193 views

Storing calculation results in DB

What I was taught in the university was we don't save the results in the database, but only the raw data which need for the calculation. For a very basic example, if: a + b + c = z we only save a, ...
1
vote
1answer
186 views

Imposing conditional entity relations

While working on an application, I am facing an issue. And I am not sure how to solve this. For explanation I have managed to come up with a simple example below: Consider a Grocery Store ...
7
votes
3answers
456 views

System Design: Very Large CSV Imports Every Month

We have a webapp that will rely on large CSVs from external vendors every month. When I say large, we are looking at around 6gb so a few million rows. Probably, 2-5 CSVs. This webapp will also allow ...
-2
votes
2answers
142 views

Are there benefits to having a SQL-like read only database layer over a Blockchain distributed ledger? [closed]

Distributed Blockchain datastores provide inmutability to recorded transactions and only allow to add records accepted by a consensus algorithm in sequence. You could store data on the blockchain or ...
3
votes
3answers
1k views

Is it OK to have a many-to-many relationship where one of the tables involved is already a junction table?

When planning our database, we ended up with a setup like this: We have Company, Product and Person. There is a many-to-many relationship between Company and Product, through a junction table ...
3
votes
4answers
635 views

Is storing files of up to 50MB in size in a database for use by multiple servers a reasonable idea? Example inside

I'm in the process of designing a server responsible for serving files that are between 10MB and 50MB in size. Initially we will run two instances of the server (lets call them fs1 and fs2), with ...
2
votes
3answers
169 views

Where to put the attributes of a relationship in a non many-to-many relationship?

Say that I have the following one-to-many relationship: If I don't want to store any information about the relationship, I can simply put the primary key of School as foreign key in the Student table....
3
votes
2answers
1k views

What's a recommended way to design a db schema for multi-language website?

I'm building a website where I plan to support multiple languages. Not only via UI, but via the content too. I have several tables where I have text columns such as "title", "name", "description", ...
4
votes
2answers
382 views

Which database technology to choose for storing (post + replies) threads

I am designing for a new application and I don't want to suffer later for the performance that manges discussion threads (posts + replies, very similar to Facebook or StackOverflow posts). I wonder ...
1
vote
1answer
828 views

Proper modeling and name convention for an intermediate table between intermediates

I have 4 entities: Event, Message, Flow and Document. Event table stores a limited (seeded) number of records. Message has many events and each event can be related to many messages. The name ...
1
vote
2answers
109 views

Database Modeling Question

I am having a doubt while creating a DB, and I would like to know if there are objective arguments that could help me mak a better decision. Case A project that can have several revisions, so I ...
2
votes
2answers
198 views

Achieving very large real-time async writes on MySQL tables without locking them to reads

I am building a webapp (Angular frontend, Groovy/Spring/Hibernate/MySQL backend) that will allow users to compete against each other over certain activities. Each activity will have 1 winner and 1 ...
0
votes
1answer
232 views

MySQL linter: Letting an installed python package Integrate with a user-written python module

I'm working on a new style of database migration system that is coming along nicely. There is one last aspect of it that I want to finish, which is to enable MySQL linting. For background, this is a ...
1
vote
2answers
67 views

Import configuration and relational data

Suppose that a user has multiple items. Each item would have complex configuration - multiple tables with relationships. Each configuration table would have an id and a item_id which is a FK to items ...
3
votes
2answers
290 views

How to Define Service Area – with Zip Codes?

I'm working on a site for at-home services. What is the best practice to define a service provider's service area? My initial thought was to define each provider’s service area as a set of zip codes. ...
2
votes
3answers
506 views

I'm trying to set up a database, to run a small pig farm. What should my events model look like?

I'm trying to set up a database, to run a small pig farm. The idea is the following: I have a table called BELLIES, where I codify the sows along with relevant information about them (weight, id, ...
2
votes
3answers
300 views

Resolving foreign keys: breaking cycles to enable a topological sort

Background to avoid the X-Y problem: I'm building a database migration system that needs to resolve foreign key constraints (see here for full background). I need to determine what order I can ...
2
votes
1answer
99 views

many to many relationships in ER Diagramme

Relational database management systems can’t have M:M relationships, so before converting a data model to a database in Access, SQL Server, mysql, or other relational DBMS, the data modeler must solve ...
9
votes
4answers
7k 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 ...
3
votes
3answers
258 views

Does it make sense to place constraints in the code instead of in the database? [duplicate]

I've recently joined a project that uses a database model which is, to me at least, unconventional. Every column, except the primary key, is nullable. Instead, the constraint is enforced in the code ...
4
votes
1answer
730 views

Design for syncing data from remote API

I am trying to find a robust and correct solution for syncing data from different sources to my local relational database. I have different sources of data for eg: CSV, REST API and probably network ...
7
votes
4answers
613 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 ...