Skip to main content

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
3 votes
2 answers
1k 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
0 answers
67 views

How to restructure a schema in order to support conditional (one of two tables) relations

I've been stuck on this for a while now so I decided to ask for some help. My SetTemplate needs to EITHER be connected directly to exerciseTemplate OR connected to exerciseTemplate through a ...
1 vote
2 answers
72 views

How to design user-defined ordering of items?

I've got a site (here) that lets users publish labs. I'm looking at adding a feature where users can customise the order that their labs appear in. To be clear, I mean that they can manually control ...
2 votes
1 answer
681 views

What would the database schema look like for a Entity Component System architecture of web app?

I found previous SE questions like this. I'm familiar with the typical RDBMS backed MVC web app framework. To illustrate what I am thinking of, let's use the examples of Bunny and Carrot from this [...
1 vote
2 answers
103 views

Linking records through multiple tables?

I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of ...
2 votes
1 answer
149 views

What is an optimal system design for tracking product views per user that is scalable?

I have a web application that contains products and users. There are 10,000+ products and 100,000+ users to give a sense of the scale that's required. For some application specific reasons, I need to ...
1 vote
1 answer
89 views

Does it make sense to use a graph database (Neo4j) for a diagramming app?

I am developing a diagramming application and want to optimize operations with the Nodes and Relations of the diagram. Currently, I am using a relational database with tables for diagrams and nodes. ...
0 votes
2 answers
193 views

Advice for database design and approach

I ask this to learn from experienced DB designers/architects. I need a high-level overview, I'm not interested in the specific tools for now. Rather, I'd like opinions on how you would approach this ...
2 votes
1 answer
183 views

Designing a flexible and comprehensive restaurant menu database schema?

I am working on developing a MySQL database schema for a restaurant menu system inspired by platforms like DoorDash, UberEats, and Yelp. My aim is to design a schema that is flexible and can handle ...
-1 votes
1 answer
50 views

Ensuring unique indexes on nosql datastores [closed]

I am building a User Service. User would be able to register with emailId or phone no. Upon successful registration, they can choose a username (which should be unique) It is easy to do on SQL ...
-1 votes
2 answers
106 views

Best Practices for Storing Operational Hours Extending Past Midnight in MySQL

I'm building a PHP application to manage business operating hours, including cases where the closing time extends past midnight. I'm considering various ways to store these hours in MySQL and need ...
-1 votes
1 answer
153 views

Would it be a good design to have multiple repositories for one entity?

I am working on a project. I have designed the codebase all the way from the scratch. Basic thing is the project is divided in individual modules. The modules represent one complete part of a business ...
0 votes
1 answer
472 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 ...
0 votes
0 answers
39 views

Need advice on approaches to saving data

The following code(JSFiddle here) is a grid-based code where I'm performing the following steps: I’m moving blue boxes from left to right (on the grid) after clicking Move Text Content! button. Here ...
0 votes
1 answer
62 views

Database Modeling for Application with Many Integrations

I’m working on an application that requires many 3rd-party integrations. I’m using Postgres to store these API responses but am having trouble for a number of reasons. These objects are frequently ...
1 vote
1 answer
481 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, ...
0 votes
1 answer
51 views

Where to store config when using a external binary in a web service?

I'm making a vpn service that can be controlled through admin web page. So I have a web page that consists of front-end page and a backend API + DB. In this admin web page, I can execute a vpn binary ...
0 votes
0 answers
123 views

Approach for comprehensive data/activity logging

I would like to be able to build up a log of user activities, capturing data such as who they were, where they logged in from, what activity did they take, and what data did they change (both before ...
-1 votes
2 answers
250 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
127 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
375 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
84 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
85 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
424 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
132 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
442 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
270 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
526 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
198 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
232 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 ...
0 votes
2 answers
632 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 ...
2 votes
3 answers
480 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
7k 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 ...
21 votes
5 answers
39k 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
240 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 ...
0 votes
2 answers
638 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
128 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
221 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
334 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
100 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
219 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
215 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
411 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
2 3 4 5
27