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 ...
- 110k
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 ...
- 15.1k
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 ...
- 2,633
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 ...
- 4,219
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 ...
- 39.4k
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 ...
- 5,271
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/...
- 78.5k
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 ...
- 212k
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
...
- 39.4k
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 ...
- 79.9k
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 ...
- 9,780
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 ...
- 16.3k
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, ...
- 110k
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 ...
- 34.3k
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 ...
- 11.6k
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 ...
- 60k
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: ...
- 805
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 ...
- 5,271
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
---...
- 131
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 ...
- 135k
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 ...
- 212k
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 ...
- 54.9k
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 ...
- 5,110
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 ...
- 28.5k
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 ...
- 617
2
votes
Accepted
How to safely allow different end users to insert data into a table stored in an Oracle database?
There are mainly two approaches to implement user management in a relational database:
use the inbuilt user/role mechanism of the database itself for managing users, so for each real person using the ...
- 212k
2
votes
Is there a way to prevent developers leaking data while they are developing?
You will see varying practice, with some more traditionally minded DBAs maybe saying at the extreme 'never let developers write SQL, wrap everything in stored procedures'. My answer, for the ...
- 166
2
votes
Accepted
Bidirectional sync between two databases with differing schemas
If this is feasible depends heavily on what 'similar' data means, and if the systems are sufficiently different, this can become arbitrary complex, and you may have to resdesign the existing systems ...
- 212k
2
votes
Accepted
Converting a table to an ORM schema
Conceptually, I'd say that it is wrong, since a person's heart rate does not have a reaction time. Similarly, a sport such as tennis does not make a booking, a member makes a booking.
- 145
2
votes
Accepted
Where should schemas be maintained?
This looks like something a good old version control system such as git could handle well.
The stuff that you're versioning is basically textual in nature, and git etc. handle evolving texts pretty ...
- 17.1k
Only top scored, non community-wiki answers of a minimum length are eligible