Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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

4
votes
2answers
134 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
83 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
25 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 ...
1
vote
1answer
53 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 ...
-3
votes
0answers
57 views

How should I handle a change of prices of an order?

Say, I have a site-shop where some products are sold. Each product has the price and the price can change. A purchase can also include shipping, discounts, taxes, etc. And they all are subject to ...
1
vote
2answers
338 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 project and I never felt the need. I did work with professional databases which had foreign keys constraints. ...
2
votes
3answers
183 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 ...
1
vote
0answers
46 views

Multiple version of database content

I am working on schedule module of a project management software, every project has two types of schedules. Base Schedule Actual Schedule The actual schedule is a copy of base schedule which is ...
0
votes
0answers
130 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
154 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 ...
3
votes
5answers
344 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
87 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
88 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
89 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
158 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
140 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
43 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 ...
0
votes
0answers
38 views

Strategies to migrate a SAP ADS database to postgres

I am looking for a good strategy to migrate an old and big SAP ADS (formerly sybase) database system to a modern RDBMS (most likely postgres). The first and foremost problem I am facing is, that the ...
7
votes
3answers
356 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
109 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 ...
2
votes
3answers
394 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
383 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 ...
3
votes
3answers
150 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
484 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", ...
3
votes
2answers
207 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 ...
0
votes
1answer
126 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
106 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
137 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
162 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
65 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
89 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
323 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
134 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 ...
3
votes
1answer
81 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 ...
6
votes
3answers
819 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
253 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 ...
0
votes
0answers
152 views

How to identify an instance of a associative entity of an ER diagram?

I am building a Library System. According to the scenario a "Member" of the library first place an reservation for a book and then "Librarian" lends the book(s) for 14 days. "Member" is charged a fine ...
3
votes
1answer
394 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
561 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 ...
2
votes
2answers
137 views

Enforcing constraints across databases

This is a design/planning question, not specific to any particular software/deployment. The stuff I'm talking about doesn't exist yet, but I am hoping to avoid mistakes early in the process. Here's ...
-1
votes
2answers
181 views

Hierarchy in ER and OO Model

So, it might be a very fundamental question, but I'm pretty confusing right now... It's all about Element and ElementCategory: an element belongs to a category, a categoty can have many elements - a ...
1
vote
1answer
92 views

Architecture/design to piece together former-relational MySQL data, now in different databases

By current design, most data is stored in MySQL, and we use the repository pattern in our web app as the layer to fetch data from the DB. We do the same thing when fetching relations, using our ...
0
votes
2answers
141 views

Should I move to In-Memory tables and stored proc or NoSql (CosmosDB) [closed]

I have a lot of data. Objects with other objects nested like this (JSON): { id: 12, Code: "a code", country: "at", prices: [{ { price: 100, from "2017-05-05", to "2017-06-05" }, ...
6
votes
3answers
776 views

Breaking up a monolith into SOA, and breaking referential integrity

When breaking up a large monolithic application with a monolithic RDBMS into a service-oriented architecture with many databases, how do you deal with the breaking of data integrity? I have a large ...
53
votes
4answers
10k views

Why is using MySQL for a dictionary website a bad idea?

I'm planning to design and set up a database to store dictionary entries (usually single words) and their meaning in another language. So, for example, the table Glossary must have entry and ...
44
votes
9answers
3k views

What do relational databases gain by setting a predefined data type for each column?

I'm working with an SQL database right now, and this has always made me curious, but Google searches don't turn much up: Why the strict data types? I understand why you'd have a few different data ...
1
vote
1answer
80 views

Do I need to include all relationships in my class diagram?

For example, I have managers who are responsible for contracts. These contracts belong to a department. So what I have done is this: manager(1) belongs to a department(1) department(1) contains ...
-1
votes
1answer
67 views

How to make sure I have the contract proposal approval in my system?

I am building a website and in it I am trying to follow the rbac (role-based access control) model. Now, the thing is that managers need to send their contract proposals to approvers (people who will ...
0
votes
1answer
296 views

Should I make a separate class diagram for the administration part of my software?

I am building a software which has two types of users in it: main users and admins. I am still building the class diagram for this system. For the main user's relations I have things like: user ...