Questions tagged [normalization]
Normalization is the process of organizing columns into tables within a relational database in such a way as to minimize redundancy and avoid insertion, update and deletion anomalies.
621
questions
0
votes
0answers
37 views
Move address data into separate table
Currently address information is stored directly in the OrderHeader and Customer tables. I would like to create a separate Address table and replace the current address information with a reference ...
-2
votes
0answers
20 views
Issues normalizing 2NF relations to 3NF
I have an assignment to submit and Im having trouble normalizing my 2NF relations to 3NF. Can anyone tell me whether this is possible? and if so, what would it look like. Im really struggling but my ...
8
votes
2answers
715 views
Should stored financial transactions include some data redundancy?
I am currently storing financial transactions in the following table (shortened for brevity):
id INT
start DATETIME
end DATETIME
rate INT
usage INT
usage_fee INT
amount INT
commission_pct INT
payout ...
1
vote
1answer
42 views
Database architecture of high frequency sensor values
I have a lot of sensors (around 1000) and want to save their values. The sensors
will mostly work around a sampling rate of 10Hz.
Regular queries will be used for a live-view application. It can be ...
0
votes
0answers
42 views
Is there a commonly accepted definition of 0NF?
The normal forms from 1NF and upwards have well known definitions in papers by Codd and others.
I have sometimes seen the term 0NF or zeroth normal form used for a data set which is not in first ...
0
votes
0answers
97 views
How to design a relational database schema for music recordings?
I would like to design a simple relational database schema for music recordings and normalize it to the ultimate normal form with respect to the projection and join relational operators, which is ...
0
votes
1answer
28 views
How can I improve this normalization to 3NF/How do you self-check normalization?
this is NOT homework, I'm just trying to improve my normalization skills. The first part is the functional dependencies i've identified and the second is what I believe the appropriate 3NF ...
15
votes
3answers
2k views
What is the reason to “normalize your databases”?
In the past, I've been told (on this site) that I should normalize the values in the database - using a lookup table instead of using direct (string) keys.
I am confused why this is so good that ...
0
votes
0answers
27 views
Is a unique “title” field against the 3rd normal form
Let's say I have a table Game, which has some random attributes, with a name, that has a unique constraint.
The Game entity contains a relationship to ExtendedGame, which contains some other data ...
0
votes
1answer
48 views
Does unique email in user table violate normal forms?
USER(userID, email, name, address, ...)
userID
primary key
incrementing bigint
email
unique identifier
can be modified by user
To me this seemed like a good idea, but then I started to wonder: ...
0
votes
2answers
45 views
How to “normalize” external data before insertion into “Data Warehouse” like database?
I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.
There are MILLIONS of email records from an email platform that wasn't too ...
-1
votes
1answer
232 views
Advantages and disadvantages of normalisation
Briefly, what are the main advantages and disadvantages of normalisation as a technique for database design?
It may be easier to answer this with a snapshot of some unorganised data, as a concrete ...
0
votes
1answer
28 views
table organization with data that will never change
for reference, almost all my DB experience is with mongodb and this is my first postgres project.
I'm storing data updates from decentralized finance crypto pools.
The data structure I'm exploring is ...
0
votes
1answer
105 views
How do I identify functional dependencies and normalize a table based on the attributes?
I'm having trouble identifying the combination of attributes which make up Functional Dependencies(FDs) of a particular table. While I understand how Normalization process works when given the FDs, I'...
1
vote
3answers
225 views
Does relational design discourage splitting tables unnecessarily?
It's been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies
K -> A
K -&...
0
votes
2answers
132 views
How to normalize a table with multiple cells that have multiple values?
So I'm doing a task where I take a massive list (30,000+) of movies on Wikipedia that have multiple columns (such as the films name, the genre, the cast, the plot etc.) and upload it into ...
1
vote
3answers
95 views
Is it allowed to for two tables to have two types of relationships between them? [duplicate]
I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So ...
-1
votes
2answers
156 views
SQL: shopping cart design for a multi vendor marketplace [closed]
I'm working on a B2B marketplace where users can order from different suppliers/vendors. The following image is a sample from my original schema to clarify the cart functionality.
The cart_item table ...
0
votes
2answers
37 views
Is it possible to store historical configuration settings for each row of data without cramming all the configuration settings into each row of data?
For background: I was recently hired as a database engineer for a water treatment company. We deploy water treatment machines to sites across the country, and the machines treat water and send ...
0
votes
1answer
25 views
Normalization using DENSE_RANK and LAST_INSERT_ID
db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, ...
0
votes
1answer
72 views
How do I prove this relation is in the `3FN`?
Consider the relation R(A,B,C,D,E) and the set of functional dependencies F={A->D,D->C,BC->AE}
I need to prove this relation is in the 3FN, however I don't think it is; in fact, I don't even ...
0
votes
2answers
86 views
MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time
I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER ...
-1
votes
1answer
30 views
If `AB->C` and `A->B` , `A->C`?
I'm new to databases, is this true? if so, how can we deduce that?
I don't know how to use the properties to get an answer, could you help>
0
votes
3answers
124 views
How can I design Person table in MySQL?
I have two person type: Users and Clients.
Users can login the panel but clients can't login the panel.
My current database: (but this design not good for me. I need to connect these tables to the ...
-1
votes
2answers
30 views
Is this the proper way to integrate normalization? [closed]
I'm trying to wrap my head around normalization.
Is this the proper way to create a database with this data? How much more effective is it to use a foreign key instead of just repeating AAPL for ...
-1
votes
1answer
115 views
How can I design address table?
Hello I need designing addresses table. I designed it but I didn't like it very much.
My needs:
I have customers and users (admins). I have addresses for customers and users. We need design in ...
0
votes
1answer
51 views
How can I design Address table with different types?
I have a Address table and different address types like Personal and Corporate. I design a table but I think is breaking normalization rules. How can I improve this design?
My design:
0
votes
1answer
56 views
What should be the schema of the database if the parent table is being referenced in more than one child table?
I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on ...
0
votes
0answers
284 views
BCNF decomposition which generates redundant relations (as for 3NF)
Sometimes the 3NF synthesis decomposition algorithm (such as the one described here p.4) generates redundant relations, where all attributes of some R_i already appear in another R_j. The algorithm is ...
-1
votes
1answer
47 views
Database scheme design for complex movie system
I'm creating a complex app for cinemas including frontend and reservation system. I struggle with a DB scheme and this is a worst part for me, I was never good in designing DB's.
Easily said it's a ...
0
votes
2answers
94 views
Database normalization for sensor data
I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns.
SQLSTATE[HY000]: General error: 1005 Can't create table xxx.xxx (errno: 185 "Too many columns&...
0
votes
3answers
49 views
How do I ensure I have split my tables without loss of data?
This is w.r.t Database Normalisation, when I have a redundant table that causes anomalies, I would want to consider splitting it in smaller tables as per the NF rules.
BUT
Say I split a big table into ...
0
votes
1answer
31 views
Meaning of “Dependency” in Relation to Elementary DB Normalization
In trying to figure out why the following table definitions from a textbook exercise about nurses in hospital wards is not in 2NF:
Ward (WName, Location, WType)
Ward-Nurse (WName(fk), NurseID, ...
-1
votes
1answer
96 views
JSON vs Record/Struct type in a DB
It seems like some databases have implement a Record/Struct type and others have implemented a Json/Jsonb type to handle nested data structures. A few examples being:
BigQuery Struct
CockroachDB ...
0
votes
1answer
114 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 ...
0
votes
0answers
9 views
How can a 'Request' store a 'Result' code and its associated 'Error' code and still enforce the relationship between Result and Error?
I'm an application developer creating the database structure to represent a flat file message format. I'd like to ask the collective knowledge the best/correct way to represent the following scenario:...
0
votes
2answers
135 views
Postgres normalize human name string
Is there some function to normalize name string:
select normalize('R.C. Bray')
'R. C. Bray'
Or the opposite direction?
The simple replace('. ', '.') or replace('.', '. ') is wrong and may be ...
0
votes
1answer
24 views
More tables or More records performance wise
i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below:
id
url
region_1
region_2
region_3
region_4
date_created
My ...
0
votes
1answer
178 views
Most efficient table structure for multi-tenant database
I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations.
We are making a change to the way our site works ...
0
votes
0answers
23 views
Can a table be un-normalizable or am I misunderstanding the theory
I was considering normalizing a flat table (SQLite if its relevant) I'd been working with in a small personal project and it just didn't seem to make sense.
Consider an example table like below. Each ...
0
votes
1answer
48 views
Relational-JSON overlapping with association [closed]
I have a composed class called sensor that is linked to a main class Node such that:
class node is linked with another node to make a communication that produce an event
class sensor can as well ...
0
votes
3answers
47 views
Is storing data that can be inferred go against data normalization?
To simplify lets say I have two tables PROJECTS and JOBS. A user creates a project and runs 1 or more jobs using a project.
So it would possible look something like this:
PROJECTS
[PK] project_id
...
0
votes
1answer
111 views
How do foreign keys affect partial and transitive dependencies (databases)
I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases.
I have multiple tables, two of which are job offer and accepted job.
in job offer,...
-2
votes
1answer
81 views
Table normalisation and empty values [duplicate]
Here are two ways I can optimise mapping of a text field with several other attributes to question and answer. First approach is, creating a map table with both question_id and answer_id in which ...
1
vote
1answer
36 views
Refactoring out a boolean column [closed]
I have a Pets table (cats, dogs, you name it) with over a million records. For simplicity sake, it looks like the following:
Pets
Field Type Null Key
id int(11) NO ...
0
votes
1answer
43 views
Database Normalization Advice [closed]
I have database for multi store application. So, I have 3 type of user thats Admin, Customer, Merchant and I have 3 table for users. User table, Customer table, Merchant table.
User Table;
id
...
0
votes
1answer
262 views
Weak Entities + Partial Keys + 2NF [closed]
I'm a bit confused on the concept of weak entities and normalization and couldn't find any answers online. I know that generally weak entities have a partial key to uniquely identify its own records ...
-1
votes
1answer
36 views
Celebrities database with multiple parent categories and children categories? [duplicate]
I am creating a React/Laravel web application that will allow the user to search and navigate through celebrities based on their 'categories'. I have set up a Categories table using MySQL like so:
*...
0
votes
1answer
23 views
Where can I get a proper dataset for performing Normalization techniques? [closed]
I'm new to databases and I just started with learning different techniques of Data Warehousing. I was given an assignment to perform Normalization techniques on a dataset of at least 1000 rows with ...
0
votes
1answer
24 views
Is it correct way having status or Summary columns in parent table or should choose another precedure?
My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:
Jobs (JobId, JobType, CurrentStage, AssignedTo)
JobActions (JobActionId, JobId, ...
