All Questions
Tagged with database-design sql
130
questions
2
votes
3
answers
479
views
Dynamically transform EAV data into standard SQL table
I use MariaDB to store data using EAV model. I need EAV, because each attribute of each entity can have its own validity (validFrom, validTo).
For example, I have entity person and I track changes ...
- 277
0
votes
3
answers
102
views
How to avoid data corruption with dual parent/child foreign keys
Imagine the following:
Persons table: (Id, FirstName, LastName)
PersonEmails table: (Id, PersonId, Address) (to allow a person to have multiple emails)
Contacts table: (Id, PersonId, UnsubscribeAll) (...
- 155
-2
votes
1
answer
481
views
Adding new column(s) to DB Table breaks existing queries [closed]
Dilemma: I am working in microservices (MS) architecture for a product with shared (PostgreSQL) DB between MSes and DB Views exposed as Data Access API between SW Components, written and maintained by ...
- 107
1
vote
1
answer
4k
views
SQL or NoSQL Database for a chat application?
currently I am working on a kind of "Chat" Application.
The app consists of "threads". Each of this threads consist of "subthreads" in wich a user can send a message (...
- 65
2
votes
2
answers
324
views
How to store queryable 10-100MB BLOBs?
I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable ...
- 129
1
vote
1
answer
171
views
SQL - store "like" counts as separate column or infer from a query?
Say I have a feature in a web app where users can create a post and like it. In the frontend the user should see the number of likes a post has.
I could store the data two ways:
1. Option 1: A small ...
- 189
11
votes
10
answers
7k
views
Is there any benefit to a separate table that is one-to-one with the primary data table?
I've inherited a system with an Oracle relational database with a couple of tables modeled like I've sketched below, where there's an entire child table that only stores a single status code in a one-...
- 366
1
vote
0
answers
125
views
Efficient way to implement hierarchical inheritance in SQL?
I am working in a database that has a hierarchy of Companies (From location-level all the way up to Top-level parent company). I am designing a system within that to provide a "subscription" ...
- 11
18
votes
3
answers
8k
views
Are surrogate keys a known anti-pattern?
My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It ...
- 1,017
-3
votes
1
answer
106
views
Where should linking tables be stored? [closed]
I have a system where properties can be stored, and linked with multiple other entities (each with their own schema). Let's say that Documents can be linked with Users and Assets. What is the best ...
- 171
-1
votes
2
answers
196
views
Difficulty understanding how denormalization results in more storage necessary
I am having difficulty understanding how denormalization results in more storage necessary.
For example, let's say that in a normalized relation, there's Table 1 and Table 2. In order to join Table 1 ...
- 153
1
vote
3
answers
1k
views
Storing count of child rows in parent table
I have two tables, parent and child. Parents can "have" multiple children—in our case they are related through a third table, parent_child_mapping rather than the child storing its parent id ...
- 471
-2
votes
1
answer
93
views
Should cached data be kept separate from primary tables?
I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for):
CREATE TABLE ...
- 337
-3
votes
1
answer
186
views
Most efficient way to get average data for thousands of records from the database [closed]
I have a database that keeps a temperature readouts, which are being added - let's say - every minute:
+----+---------------------+-------------+-----------+
| id | timestamp | temperature | ...
- 149
1
vote
1
answer
532
views
SQL Foreign Key Relationships and External Data
I work for a company that wants an external system built outside of their core system. I can't share an SQL database, and can't store data in their database. So I am forced to maintain my own SQL ...
- 127