Questions tagged [denormalization]
Database denormalization is the action of adding redundant data to database for performance optimization. This is the opposite to normalization where data is split into separate tables.
85
questions
16
votes
1
answer
7k
views
Designing a database for a video game business domain with multiple many-to-many relationships
I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous ...
- 193
12
votes
6
answers
34k
views
Possible benefits of storing multiple values in one field of one row instead of as separate rows
During our last weekly meeting, a person that has no background experience in Database Administration brought up this question:
"Would there be a scenario that justifies storing data in-line (...
- 591
10
votes
3
answers
2k
views
Integrity constraints in a relational database - should we overlook them?
I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a ...
- 1,710
8
votes
4
answers
987
views
Store millions of rows of denomalized data or some SQL magic?
My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know!
I have a problem whereby I need to lookup or calculate ...
- 223
7
votes
6
answers
3k
views
Storing personal data on an invoice to preserve data integrity
I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data ...
- 209
6
votes
1
answer
866
views
Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication?
I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables.
The designer's answer was that splitting CSV columns into separate tables was "...
- 429
5
votes
5
answers
12k
views
Normalization for postal addresses in US (address, county, city, state, zipcode)?
I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get ...
- 203
5
votes
3
answers
1k
views
Replace relational database with "poor" design in software product with non-relational DB? [closed]
Edit: This question is about how to deal with many issues that arose from a overall system design, which made parts of the system deviate from common standards. For example, managing everything in the ...
- 500
4
votes
2
answers
279
views
Is combining independent tables called normalisation?
So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc.
These are all independent data with no foreign ...
- 143
4
votes
4
answers
2k
views
Should I Denormalize Tables To Avoid Joins?
I'm in the process of developing a web application, and need some help with some basic db design. Currently, I am unsure of the following three tables:
### User ###
- Id
- DetailId
- etc... (About ...
- 49
4
votes
2
answers
359
views
Two primary keys but not a composite, what is this sort of relationship called?
I am integrating employee/position data with another system.
The first system is HR. It has an Employee table and a Positions table. The second system tracks office space and tracks who the office ...
- 443
4
votes
1
answer
871
views
Do I have to (sometimes) sacrifice normalization for data integrity?
I often come up against the following challenge, and end up de-normalizing my table design in order to enforce data integrity. I would be interested to hear if there is a different way of modelling ...
- 315
4
votes
2
answers
884
views
Denormalized tables within OLTP database
My company has a product centered around a fairly normalized mssql server database. At this point we have several reports and a web application that needs the data in a much flatter fashion to ...
- 251
4
votes
2
answers
2k
views
Database Normalization
If someone could guide me on helping me understand if my results are correct I would appreciate that. Let's say I have the following table:
BOOK (BookTitle, AuthorName, BookType, ListPrice, ...
- 41
3
votes
2
answers
927
views
Is it a good approach to duplicate data in a database?
I have a program that needs to store traceroutes (among other data).
Here is a diagram that depicts the business scenario:
My use case right now is to store the traceroutes using the following ...
- 133
3
votes
1
answer
8k
views
How can I prove / disprove If A ↠ BC and A → B then A → C
/ From GATE 2007 IT:
Consider the following implications relating to functional and multi
valued dependencies given below, which may or may not be correct.
A) If A ↠ B and A ↠ C then A → BC
B) ...
- 33
2
votes
2
answers
283
views
How can I optimize this query over this logging table?
I am trying to optimize a table layout for a table that logs events.
The log table contains three relevant columns: Timestamp, ItemId, LocationId
Each row means that at a given time, a certain item ...
- 361
2
votes
3
answers
193
views
Storing Procedure Fields That Were Used
I am creating a database for production work (specifically lab testing).
Most Work is for production, therefore performed strictly according to the Procedure for that Product. By itself, this is easy ...
- 319
2
votes
2
answers
4k
views
Best practice database design - user rating tables
I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this:
IID | UID | Rating
i.e.
item ID |user id |rating.
From ...
- 21
2
votes
2
answers
55
views
Normalizing data past initial db design phase
I wanted to poke at everyones brain to better understand when it makes sense to normalize a table that carries redundant data. I recently worked on a project at my startup and really question myself ...
- 21
2
votes
1
answer
161
views
Redundant FK on parent relationship is denormalization?
Let's say I have this database
client
| id | status |
sale
| id | client_id | date |
sale_item
| id | sale_id | *client_id* |
The *client_id* column is redundant, because we can JOIN and get ...
- 123
2
votes
3
answers
567
views
Are updates the only disadvantage of denormalization?
I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table):
I have read that the problems that you ...
- 129
2
votes
1
answer
434
views
Duplicate data or Relationships?
Given two tables:
Product
int id
int operator_id
string name
string description
int price
Operator
int id
string logo_path
string tos_path
int tax_number
I need to call multiple ...
- 123
2
votes
2
answers
2k
views
Data normalization (from 0NF to 1NF)
I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this
"A relation ...
- 131
2
votes
2
answers
82
views
unusual relation, binary aspect
I have question very unusual for databases. I’m not a good English speaker, but good English reader :) so for explanation I’ll give you examples.
Part1
In MySQL, I have two tables with relations ...
- 53
2
votes
1
answer
79
views
Denormalize Design Modification
Our DBA is on medical leave, I am just a developer trying to do my part. Initially, the bean counters were going to use quickbooks. However, after seeing how much time they could save with custom ...
- 121
2
votes
1
answer
517
views
Should a counter column with frequent update be stored in a separate table?
I have a MySQL/MariaDB database where posts are stored. Each post has some statistical counters such as the number of times the post has been viewed for the current day, the total number of views, ...
- 33
2
votes
2
answers
57
views
Schema For "Other - Add Comment"
Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list.
In other words, the developer sets the list of items. The user must either ...
- 319
2
votes
0
answers
542
views
Normalization vs denormalization for these kind of data?
I'm using Firebase realtime database (NoSQL), And trying to figure out the best database design for my data.
My data is primarily about discounts, each affiliator introduces a number of discount ...
- 121
2
votes
0
answers
59
views
Is there any literature on "re-normalisation" of denormalised data?
I'm involved in a project, the core of which might be generously described as "re-normalisation" of denormalised data. (To be fair, the initial implementation took only the first 90% of the time ...
- 121
2
votes
1
answer
171
views
Should I split my tables into different tables when all of the data is dependant on the primary key?
For my assignment I have to make a database for my licensing application. I have 12 columns and one column is for the license key. All other columns are data that is related to the license key. The ...
- 23
2
votes
0
answers
428
views
Database sharding, denormalization and synchronization
In sharding, it is advisable to follow a share nothing, shard everything approach. Essentially, this translates to: data that are to be used together are to be stored together (i.e. in a single shard)....
- 779
2
votes
0
answers
58
views
How To Manage Random Data Without Repitition?
We have a database design problem of a Learning System.
Please can somebody help us ?
These are the requirements :
A person can register for a module which has "n" number of questions.
The person ...
- 21
2
votes
1
answer
202
views
What should we do when same column fields occurring multiple time in many tables?
I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better ...
- 123
2
votes
0
answers
416
views
Is the table in 3NF?
I am creating a relational database - a library management system. I want to show an example where I normalize the data from 2NF to 3NF. My example of 2NF looks like
The definition of third normal ...
- 63
2
votes
0
answers
105
views
Denormalizing to speed up inserts
I have a MySQL InnoDb table with approximately 40 million rows and the following columns:
param1 (int),
param2 (int),
param3 (int),
geo (char2),
value1 (bigint),
value2 (bigint)
I have an ...
- 133
1
vote
3
answers
719
views
When is it beneficial to move duplicate data to a reference table?
If there is column with values from some finite set S, for example S = { alive, dead, unknown } for a life_state column in a persons table, when is it beneficial to add a reference table?
Options:
...
- 449
1
vote
2
answers
1k
views
Why is data integrity not possible when using denormalization?
This tutorial says the following:
For proponents of denormalization, the thinking is as follows:
normalization creates more tables as you proceed towards higher normal
forms, but more tables ...
- 119
1
vote
2
answers
2k
views
Need advice on database normalization
I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further ...
- 13
1
vote
2
answers
773
views
SQL Server vs SOLR (Or any document db)
I have my "customer" data in a normalized sql server database.
Getting out the customer data in my app is taking too long. This is because I have to go to 10+ tables to get all the data I need.
My ...
- 2,484
1
vote
3
answers
1k
views
Best way to create relationship between multiple tables
I have a question about how to best handle a table that would be shared among multiple relationships, but shouldn't really be joined with all these tables. For example, suppose I have a table that has ...
- 127
1
vote
1
answer
241
views
Junction table or denormalize?
I'm using MariaDB.
I'm designing a simple application that can send notifications to subscribers for when certain websites they've subscribed to have been updated.
A user can be subscribed to several ...
- 211
1
vote
1
answer
1k
views
database design - table with multiple nullable foreign keys
Context:
I have 3 unrelated entities (tables) Entity1, Entity2 and Entity3
I have a table called Operation, which can have multiple targets.
A target for an operation can be Entity1, Entity2 or ...
- 119
1
vote
1
answer
186
views
ALTER TABLE online by keeping data as BLOBs
Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source
Additionally, it's crucial to be able to change the schema: ...
- 407
1
vote
1
answer
315
views
Database Over Normalized [closed]
How can you tell when a database is over normalized? Example: I have a database for employee. These are the following tables involved:
User - contains email, password, companyId
Profile - profile of ...
- 179
1
vote
1
answer
109
views
Will a specific denormalization increase performance in a specific scenario?
Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which ...
- 111
1
vote
1
answer
79
views
A clarification about data normalization
I've been a software developer for 8+ years now and I think I have a pretty good grasp of the concepts but as an entirely self-taught developer, I have some unfortunate gaps in proper terminology. ...
- 111
1
vote
1
answer
102
views
Can I use Azure Data Lake in place of Data Warehouse to separate Compute and Storage of RDBMS analytics [closed]
I am from a telemetry data engineering background and am looking for some help on a use-case I am not familiar with as to whether my proposed architecture is practically feasible in Azure. The use-...
- 11
1
vote
1
answer
141
views
Database normalization vs several dozen joins [closed]
Using PostgreSQL I have a few tables that are about 22,000 records and about 275 columns. These tables are actually reports. There are about 25 sections in each report containing anywhere from 4-12 ...
- 353
1
vote
1
answer
3k
views
Normalization from UNF to 3NF
I keep getting confused trying to normalize this data.
A company makes and sells a product online. There are ten types of Products, each having a code and price.
Their Sales Order department takes ...
- 21