Questions tagged [database-design]

For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

Filter by
Sorted by
Tagged with
-1 votes
2 answers
149 views

Should I store large amounts of data in the properties of a node in a graph database?

I'm building a reddit clone on top of software that must incorporate a legacy neo4j graph database to track user actions. I also want to use it to store the comment/post data. Assuming this new app is ...
2 votes
1 answer
71 views

Handling Back-dated Transactions and "Online" Transactions on ERP/Banking Systems

I was currently developing a PoC of a "core banking system" in my current company, a pawn shop that generally offers lending services to retail customer and deposit services. We've built a ...
0 votes
1 answer
90 views

Clarification on using timestamp without time zone and LocalDateTime in UTC-centric systems

I'm seeking clarification regarding the usage of timestamp without time zone in a database and its corresponding Java type, LocalDateTime. The current setup I'm working on involves servers and (...
0 votes
1 answer
67 views

Better way to track and analyze downloads (or any other events)

I need to decide about my database architecture. The app provides files for download. I can add a counter to every file and track total events. But the client may want some analysis and ask for the ...
-2 votes
1 answer
71 views

What is a route I could take when considering setting up a data warehouse for a small business? [closed]

I am working for a medium-sized development and construction company and we are looking to upgrade our data and BI. Currently, all project data is managed via Excel (data, project management, and ...
6 votes
1 answer
418 views

How to set up a sql database to cater for user records, group records and default records?

Outline I have an application that loads data from a database. I'm not talking about client data here though, I'm talking about application configuration. The database will therefore come with some ...
1 vote
1 answer
114 views

Monolithic Database to Microservices, but with Radical Data Model Changes

We are finishing up a long (over 1 year) project to retire our monolith. We have been moving pieces of functionality into microservices guided by domain driven design. So far we have been leaving the ...
3 votes
1 answer
434 views

Integration database on micro-services

I need some wisdom on this. Currently I'm learning about micro-services architecture, and so I decided to make a simple project in a micro-services architecture. The project I'm building is just a ...
0 votes
1 answer
97 views

Designing a graph database structure for an application with a large amount of connections

I'm looking for advice on how to design the data model for a project I'm working on. I'm not really sure where to ask such a question. I've been using StackOverflow for years, but this problem is a ...
2 votes
3 answers
1k views

Is this a good implementation of mongodb for a money management tool?

I have a money management tool which has users. Each user may have several accounts/funds (e.g. bank account). For each account/fund there may be thousands of transactions. Users may only view their ...
0 votes
1 answer
505 views

How to organize users data in NoSQL database for a user invite feature (even before a user sign up)?

I am doing a collaborative note editing app which uses a NoSQL database (Firebase) in server side. Each user can sign up using their phone number or email. Currently, the user object has only four ...
-2 votes
2 answers
141 views

Designing a database schema for a website related to my town's history

I am working on digitizing old photographs, articles, ephemera, etc. related to my town's history, and I've been tasked to put these scans I've made onto a website for the public to see. There are ...
1 vote
2 answers
221 views

How do I test a method which interacts with a database?

So I have a database class which has methods dedicated to specific actions. For example I have below method which accesses and writes to a table in the database: def trackUserPerformance(self, ...
12 votes
5 answers
7k views

Is it an anti-pattern to create ORM entities based on existing database schema?

I maintain a large legacy app working with SQL database. The app uses raw SQL queries to query the database. Together with app rewrite I plan to introduce ORM to work with the database. The simplest ...
-1 votes
2 answers
238 views

How to effectively scale one microservice to multiple instances

Let’s say I have a booking microservice (single instance) with its own DB. If I want to horizontally scale the service with multiple instances(exact same copy of the business logic code) running on ...
0 votes
1 answer
457 views

Regarding data modeling in a NoSQL use case

I am reading the following link to learn about system designs of various systems. (This is a paid link, I am attaching all explanations below.) In an attempt to explain the system design of Instagram ...
2 votes
3 answers
338 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 ...
8 votes
4 answers
5k views

Benefits of created_at or updated_at columns in database design

I am under the impression that having created_at or updated_at columns seems to be a best practice in database design. Frameworks like "Ruby on Rails" and "Laravel" both support ...
2 votes
1 answer
446 views

Design question on synchronization of two asynchronous data streams

I have two async streams suppose- Trip : {tripId, date, city} Bill : {billId, tripId, date, amount}. I need to design a system to get real time aggregated view of following nature: City, TripCount, ...
20 votes
5 answers
36k views

What is the best practice for saving TimeZones in the database?

We want to start collecting timezones for each of our addresses in the database. What is the best practice to store the timezones? How would you go about acquiring timezones for the existing address ...
2 votes
2 answers
232 views

How to design fault-tolerant distributed "all-or-nothing" system?

I have a system where Client(C) sends request to Server(S0). S0 then sends the response back to Client that "request received" and closes the connection. C can regularly poll S0 to check on ...
3 votes
2 answers
962 views

Best way to structure many-to-one allocation of users to projects in NoSQL

I apologise if this is a bit of a stupid or broad question, but I'd like some general feedback please! Say I have this DB structure for users (in Mongo/JS, but really it's all pseudocode- and the IDs ...
0 votes
2 answers
553 views

Structure database for timeline with different objects

I need to work on a project where we have a "case". Within this case we need to attach different things, in a timeline. Those things can be: Status change (Open, On hold, Closed) Messages (...
3 votes
2 answers
115 views

how to deal with schema/constraint changes in insert-only Relational DBs?

as per request, I'll reduce this whole set of questions to 1-2 core points (thanks for the feedback ;)). I'm considering using insert-only tables for keeping the history of my data. while the ...
3 votes
2 answers
5k views

Suitable data store for storing comments in a social media site

I have been developing a blogging site, where people can post an article or blog and others can comments on them. The site is coded in Angular.js, ASP.NET and SQL is being used a data store. For each ...
15 votes
4 answers
5k views

Better solutions than joining table for Many to Many?

Lets say I have students and classes which are 2 entities. A student can take many classes and a class can have many students. This would be a many to many relationship. To solve this with an RDBMS my ...
0 votes
1 answer
174 views

Database design for different type of user comment

I'm designing a database where a user can add notes on diffrent entity of the application. For example, the user can add a note on the project and/or on the project line. The note added can have ...
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 ...
15 votes
4 answers
3k views

Should entities contain information about their amount?

Can I get some thoughts on the entity design? Let's say I have an entity called Book. Let's say I create a specific instance of that book. It has a title A and an author B. Since there can be multiple ...
1 vote
3 answers
6k views

How to store product and pricing history in a product database?

What is the best way to store historical information about products and pricing in a database? Our suppliers provide us with catalogues in various formats that usually contain manufacturer's ID of ...
1 vote
4 answers
325 views

Is it a good practice to allow null FKs?

I have Projects table. Projects can have costs, so I have a Costs table. A cost can be because of a product or beacuse of a service. So I have 4 main tables: Projects (IDProject, Description...) ...
0 votes
0 answers
81 views

Data modeling - strategy game - over-normalization?

I am trying to build a travian-like web browser game (static strategy game). I am currently working on my tables and data model. I don't have much experience with relational databases and I am ...
-2 votes
1 answer
201 views

Designing a facial recognition systems database

First of all, I hope im in the right stack exchange. So I'm trying to build a facial recognition system, e.g. one that recognizes a face and compares it to a database of known faces. For the first ...
0 votes
2 answers
118 views

When should we separate common parts of multiple services into one separated service

We have some services (consider 10, some of them already exist and some are yet to come). All of these services have a common part where they keep track of what's being changed, and these so-called &...
1 vote
1 answer
269 views

Representing Rust enums in databases

Although I love Rust's enums (and the idea of making illegal states unrepresentable), I'm struggling to represent (ironic, yes) them in databases like PostgreSQL when the variants have data associated ...
1 vote
2 answers
436 views

How to manage primary keys in CQRS

I'm building a backend following the CQRS pattern and I don't know how to manage properly the primary keys (surrogate keys) between the command and query databases. For example: I have a model with ...
1 vote
1 answer
203 views

Thoughts on adding a DB collection with a single record for version

I'm building an application with Google Firebase and Firestore that has its structure defined in the DB. This structure is not very big and doesn't change all that often. This structure is stored in ...
1 vote
1 answer
183 views

Database design for graph where nodes can be other graphs

How should I design a relational schema to store graph documents that can contain plain nodes (shapes, in the example below) as well as nodes that can reference other graph documents? To visually ...
-1 votes
3 answers
384 views

Are there existing term(s) for a 1-1 child-parent table anti-pattern?

NOTE: Please don't respond by telling me that I probably don't understand what I am looking at. You can't possibly know that and it's wrong. Just don't answer if that's all you have to say. I'm ...
0 votes
0 answers
39 views

Cascading ACL to embedded objects

We have an object Root, which references many other objects. Which eventually is translated into FKs on the DB level. We came up with an ACL scheme where there's a separate permissions table with the ...
1 vote
1 answer
164 views

deciding where to put "formatting" logic - database or in application layer

We have an application that fetches user data from an Azure tenant via MS Graph. We need to distinguish between guests and regular members. Guests come back with Usernames that look like this: ...
0 votes
0 answers
37 views

Using Final table and triggers to generate a appointment schedule or generate it on the fly? [duplicate]

I am making a appointment system for various businesses. Each business provides us the following type of information (system already impemented): Weekly schedule which day of week id open and close ...
1 vote
1 answer
309 views

How can I restore an entity from the database while keeping my entities database-independent?

Background I'm currently working on a project using domain-driven design and Dapper as my ORM. The entities are naturally slightly different than the tables in which they are stored (e.g. _id is ...
1 vote
1 answer
123 views

Is it s agood idea use as foreign key two fields instead of the primary key?

I have this two tables: Documents(ID, IDTypeOfDocument, BigIntReference); I have a documents table that has and ID that is an autonumeric bigint. Also I have categories, and also it has a big int as ...
5 votes
1 answer
3k views

Best way to store quite large JSON objects?

I'm building an app where I need access to quite large reports about different publicly accessible URLs, JSON objects about 200kb to 500kB in size. Each user would generate hundreds of these reports ...
0 votes
0 answers
59 views

Supabase Web App DB Design

I have the following Supabase db structure for my web app Project -> Stage -> Logbook A project can have many stages (stage has a foreign key for project) A stage can have many logs (logs have ...
2 votes
2 answers
5k views

Entity Framework Code First, C# class separation and EAV

I'm in the process of redesigning a portion of my ASP.NET MVC application. I'm currently using Entity Framework 6.1 code first approach. I've been reading as of late that (Correct me if I'm wrong, I ...
0 votes
3 answers
100 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) (...
0 votes
1 answer
64 views

Secondary indexes vs Using elastic search

When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore? Elastic search with another primary store Pros: Primary datastore can be optimised for read ...
2 votes
4 answers
721 views

What's the best way to save/track change requests on multiple objects in a database?

I have a schema containing contracts, sub-contracts, services offered by each of those, and sub-services. I also have an application that allows me to extend the duration of the contract and to modify ...

1
2 3 4 5
27