Skip to main content
48 votes
Accepted

Better solutions than joining table for Many to Many?

Quote of the day: Premature optimization is the root of all evil- C.A.R. Hoare There is no reason to assume that the join will be slow, if you made sure that the join table is indexed on its both ...
Christophe's user avatar
  • 78.3k
22 votes
Accepted

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

Reverse engineering was created for precisely your kind of situation. It was meant to be a time-saving tool when using an ORM with an existing database. The tool can do in a few minutes what a human (...
Greg Burghardt's user avatar
19 votes

Better solutions than joining table for Many to Many?

I could query the joining table for this information but I imagine queries would be slow since there's no clear way to horizontally partition the data. Assuming the Primary key on this table is { ...
Phill  W.'s user avatar
  • 12.4k
9 votes

Better solutions than joining table for Many to Many?

I’m going to jump on the premature bandwagon, but from a different direction. I know your students and classes are placeholders for something else, but… How many students and how many classes are we ...
jmoreno's user avatar
  • 11k
6 votes

Is it a good practice to allow null FKs?

This design is perfectly valid. It is called Single Table Inheritance, because you merge in a single Cost table two specializations which are CostForService and CostForProduct. The pro is that you can ...
Christophe's user avatar
  • 78.3k
6 votes

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

Both patterns are supported. Code first versus Model first. If you have an opiniated database administrator(s) or strong SQL skills one may choose Model first. If one has less SQL expertise, ...
Jon Raynor's user avatar
  • 11.6k
5 votes

Dynamically transform EAV data into standard SQL table

Is this a good approach or is it better not to use EAV model at all? I think I'd be on strong ground in saying that most practitioners disfavour EAV, and find that its challenges outweigh its sole ...
Steve's user avatar
  • 9,012
5 votes

Monolithic Database to Microservices, but with Radical Data Model Changes

You've already analyzed the possible approaches, including their justifications. All that's left to do is to decide which decision makes the most sense in your business context. Internet strangers ...
Flater's user avatar
  • 52.3k
5 votes

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

I am not a Neo4J expert, but I can tell you how I would approach such an assessment: Ask in a place where you find other Neo4j users and experts: the Neo4J community site looks good for me: https://...
Doc Brown's user avatar
  • 209k
5 votes

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

tl;dr: Use an RDBMS, as there's nothing in the problem statement to motivate neo4j or similar graph databases. primary key You said you have 40,000 rows in world_fact, things that are immutably and ...
J_H's user avatar
  • 7,320
5 votes
Accepted

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

Your design doesn't sound great to me. The idea with a repository is to abstract the underlying database. With your "generic repository" design you have a repository per entity already, so ...
Ewan's user avatar
  • 77k
4 votes

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

Who "owns" the database? Is it your app? Or some other business? Do you have a continuity plan to support the old app while you develop new one? Unless the database is for your app only, and ...
Thomas's user avatar
  • 49
4 votes

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

Neither is an anti pattern, they are just two different mindsets regarding database integration: The application centric: The database is just a service for persisting application state. The role of ...
JacquesB's user avatar
  • 59.8k
4 votes

How to effectively scale one microservice to multiple instances

What efficient changes can be done on the DB side? The first thought that comes to mind is maintaining separate DB instance for each instance (since they are running on different servers) Absolutely ...
pjc50's user avatar
  • 13.7k
4 votes
Accepted

Designing a flexible and comprehensive restaurant menu database schema?

I look at it this way; your root item is your "Menu Item", and everything else is basically a "variation" of the menu item; from sizes, to add-ons, to options. I think there's ...
Darren Taylor's user avatar
3 votes
Accepted

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

The solution to this kind of problems is to be found in a two phase commit protocol. If the fact that 2PC requires a master coordination node is an issue, you can consider a three phase commit ...
Christophe's user avatar
  • 78.3k
3 votes

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

The historical insert only table should only have 1 constraint, and that is to the primary key of the record that it is keeping tracking of. No other constraints/FKs should exist, even though they ...
Jon Raynor's user avatar
  • 11.6k
3 votes

Is it a good practice to allow null FKs?

I want to explain one specific use case here, because the implementation of said use case is relevant for you to consider. Entity Framework allows you to model inheritance in your database. What you'...
Flater's user avatar
  • 52.3k
3 votes

Is it a good practice to allow null FKs?

I'm finding it a little difficult to follow your design without some sort of entity diagram, but I think you might have the relationship between Costs and the associated Products and Services ...
JimmyJames's user avatar
  • 27.5k
3 votes
Accepted

Dynamically transform EAV data into standard SQL table

In a comment, you wrote Business domain of the app is school agenda (students, classes, etc). Each entity has about 50 attributes, almost every of them is potentially changeable (surname, e-mail, ...
Doc Brown's user avatar
  • 209k
3 votes

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

You should consider using an existing content management system instead of basically rolling your own. Most have functionality for managing media, including analysis and maybe indexing of metadata.
Hans-Martin Mosner's user avatar
3 votes
Accepted

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

I would do away with the database altogether and think of a way to store the image metadata in either EXIF or XMP data alongside or inside the image itself. For searching through the images, I would ...
RibaldEddie's user avatar
  • 3,283
3 votes
Accepted

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

I'm kinda confused because in web app, I store configurations in DB but then it needs to be copied to the config file for a binary to receive. It looks like violating SSOT(Single Source of Truth). ...
Bart van Ingen Schenau's user avatar
3 votes

Best Practices for Storing Operational Hours Extending Past Midnight in MySQL

I've done this before and it's complex. You might have missed some edge case requirements Days longer than 24h Businesses with stores in different time zones. Daylight Savings where you have more ...
Ewan's user avatar
  • 77k
3 votes

Ensuring unique indexes on nosql datastores

tl;dr summary: The way to model unique composite indices in NoSQL is with … unique composite indices. How can this use case be modelled on nosql datastores? It doesn't make sense to talk about "...
Jörg W Mittag's user avatar
3 votes

Advice for database design and approach

At first, I imagined two related tables, one for the owners and another for the buildings. The Owners table would have columns such as Owner ID, name, surname, amount of buildings, tax codes, history ...
JimmyJames's user avatar
  • 27.5k
2 votes
Accepted

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

Your second design is good. Keep timestamp + filename forever. Index on at least timestamp. Is the table “too big”? No. Dashboard queries for “today” or even for this week’s downloads ignore the bulk ...
J_H's user avatar
  • 7,320
2 votes

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

where the data will be hosted by the software company The problem with that approach is that you end up effectively undoing that arms-length cloud hosting when you want to draw things together for ...
Steve's user avatar
  • 9,012
2 votes
Accepted

Database design for different type of user comment

The short answer to your question is: It depends upon your use cases. If every time you lookup notes, you always do it in the context of a particular entity - i.e. I want the notes associated with ...
DavidT's user avatar
  • 3,348
2 votes

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

If the shared part is extensive, you could use a Monorepo so that the logging code is maintained only once, but deployed in several different microservices. This avoids adding network logic to what ...
Kilian Foth's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible