119 votes

Should I define the relations between tables in the database or just in code?

The database doesn't have to check for data integrity every time application modify data. This is a deeply misguided point. Databases were created for precisely this purpose. If you need data ...
Kilian Foth's user avatar
71 votes
Accepted

Should I define the relations between tables in the database or just in code?

TL;DR: Relationship constraints should go in the database. Your application ain't big enough. You are correct, indeed, that enforcing relationships across databases may require enforcing them in the ...
Matthieu M.'s user avatar
  • 14.9k
52 votes

Should I define the relations between tables in the database or just in code?

The constraints should lie within your database, as (with the best will in the world), your application will not be the only thing to ever access this database. At some point, there may need to be a ...
Paddy's user avatar
  • 2,623
17 votes

Should I define the relations between tables in the database or just in code?

You should have relations in the database. As the other answer notes, performance of constraint checking will be far better inside that database than inside your application. Database constraint ...
Kirk Broadhurst's user avatar
13 votes

Should I define the relations between tables in the database or just in code?

We no longer live in one back-end <-> one front-end world. Most solutions involve a web front-end, a mobile front-end, a batch-front-end, and iPad front-end, etc. Database engines already have ...
Tulains Córdova's user avatar
11 votes

Use UUID on top of auto increment ID

The biggest myth when designing applications is that you are only allowed to have one key. Have multiple keys, go ahead and do it. Your application is allowed to have a different primary key than ...
TheCatWhisperer's user avatar
8 votes

Use UUID on top of auto increment ID

There is no point having two primary keys. UUIDs are slow to index. No they are not. Stop worrying about arcane numbers like pages and fragmentation and do some tests. https://www.mssqltips.com/...
Ewan's user avatar
  • 75.8k
6 votes

Is there a way to prevent developers leaking data while they are developing?

In larger organizations it can sometimes make sense to establish different access levels to different parts of a database for different groups of people. That's why most relational databases usually ...
Doc Brown's user avatar
  • 207k
5 votes

CMS database schema design advice

A CMS model looks like something complex but lets try to think up something. It seems that the relationship between POST and POST_VERSION should be 1:M LANGUAGE entity is missing ...
Tulains Córdova's user avatar
5 votes
Accepted

Is it recommended to use schemas on non-relational databases?

Schemaless is a misleading term: On one side, a SQL database always has a well-defined schema. This schema is fixed. If you want to put more information in a "record", you'd have to alter the ...
Christophe's user avatar
  • 77.2k
4 votes

SOLID principles and generating many objects from a file

If I hand my blacksmith a hammer, that means that the blacksmith doesn't need to know the finer details of which hammer to get; I just need to hand him the most appropriate hammer for my intended job. ...
Robert Harvey's user avatar
4 votes

"Expires_at" column: amount of hours or the final value?

It depends on what you're trying to accomplish with expiration. Is keeping track of a period of validity interesting to your app over a long period of time? E.g. you could have many differing ...
Jonathan Eunice's user avatar
4 votes

Using a Single Collection for the entire application in MongoDB

Bags Well bags are very easy, you throw everything inside. You don't have to make any upfront decisions. Updating is a breeze, just throw it in, or take it out. Because everything is in the one spot ...
Kain0_0's user avatar
  • 16.2k
4 votes

Bidirectional sync between two databases with differing schemas

You should not do this. Doing a unidirectional sync is challenging, adds a pile of constraints to your system, but is a fairly well known problem at this point. When you make things bidirectional, ...
Telastyn's user avatar
  • 109k
4 votes

Appropriate database schema

With either of those schemes, if you want different samples of the same flat, you'll have multiple rows with the same FLAT_ID.  Essentially your primary key will be (FLAT_ID, SAMPLE_DATE). Regarding ...
Erik Eidt's user avatar
  • 33.8k
3 votes

"Expires_at" column: amount of hours or the final value?

A good rule of thumb: If you are in doubt, use the simplest possible solution. In this case having a single expires_at which is an unambiguous DateTime value is clearly simplest. Introducing units ...
JacquesB's user avatar
  • 59.6k
3 votes

SOLID principles and generating many objects from a file

The questions to ask in designing: What information does Blacksmith need when they are "building" an object? How do the different things a blacksmith can make affect the behaviour of ...
HorusKol's user avatar
  • 4,131
3 votes

How to define and share a JSON schema between the front-end and back-end of an application?

We use json schema to validate the objects we send around. In case you have functional dependencies in a schema, in Python, the library allows you to use functions to validate sub-entries. Example: ...
User's user avatar
  • 795
3 votes

Using a single table for identity and metadata

You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that ...
TheCatWhisperer's user avatar
3 votes

Database Schema for Pricing Products (packages, promotions, qty based, limited time offer...)

Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody. Table Structure Table_Offer -------- ID FK Name start time end time MandatoryGroup ---...
123456's user avatar
  • 131
3 votes
Accepted

Generate HTTP RESTful server from database schema

Today the job of most Java-based servers is to translate HTTP requests into SQL commands/queries and send a JSON-ed response back to the client. Do you have a source to back this up? In my ...
MetaFight's user avatar
  • 11.6k
3 votes
Accepted

Efficient Schema For Pairing Styling Metadata with Text Fields in JSON Messages

There are many reasonable approaches I have seen. Markup languages are often quite reasonable, and often not that hard to parse. Typical choices include Markdown (CommonMark) BBCode safe HTML subsets ...
amon's user avatar
  • 134k
3 votes

How do you test whether schema changes will break services?

Let us assume the worst case: your services are "black boxes", written by some third party which isn't available any more, and you don't have access to the source code. Then your best option ...
Doc Brown's user avatar
  • 207k
3 votes

How do you test whether schema changes will break services?

Shielding data access However, a number of "services" consume this data. Your situation is the gold standard of examples for why this advice exists: A database is an implementation detail ...
Flater's user avatar
  • 49.9k
2 votes

SOLID principles and generating many objects from a file

You just hit one of the more nuanced problems with SOLID. That it greatly increases complexity of your application. Especially dependency injection. But you get much greater flexibility with your code ...
Euphoric's user avatar
  • 37.4k
2 votes
Accepted

How to model a property with two different formats and ranges

Let's just say in a year from now you decide that you need support em or whatever other type for width. For compatibility I would go for the first alternative as I think this change might be ...
Simon Kraemer's user avatar
2 votes
Accepted

versioning data schema in android internal storage

It's hard to answer this with the information provided. For instance, you say that you're "stringifying" the data, but you aren't saying what method you're using. Is there a library you're using? Is ...
MrMannWood's user avatar
2 votes

Define a RESTful API for creating/updating other resource definitions?

The simplest solution here would seem to but to call PUT on the user resource with the new version of the schema. This is a problematic in general because it's subject to dirty reads. This is also ...
JimmyJames's user avatar
  • 27.3k
2 votes

Should I define the relations between tables in the database or just in code?

If you don't validate your data integrity, constraints, relationships etc. at the database level that means it is much easier for anyone with production database access (through any other client ...
Bradley Thomas's user avatar
2 votes
Accepted

Database Schema design of server running status log

Whatever you do, you should certainly not be creating a new table for every instance. The simplest solution would be to just not use Instance ID as the primary key, and instead have an autogenerated ...
Sean Burton's user avatar

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