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.

Filter by
Sorted by
Tagged with
-1 votes
0 answers
37 views

Is a table normalized if there is an enum in one of its datatypes?

The accepted answer to this question says that the table that had an enum as a datatype should be "normalized". Is there any rule that a table is not in 1NF/2NF/3NF/BCNF/4NF/5NF if there is ...
user avatar
  • 101
-1 votes
2 answers
34 views

How to design to avoid duplicate Foreign keys

I wasn't really sure how to title this question. I have three tables User, Photo and Album. A user has many Photo objects and many Album objects. However, after a User has added a given photo they may ...
user avatar
0 votes
1 answer
32 views

Is there a dependency preserving, lossless BCNF decomposition for this relational schema?

R(A,B,C) where {AB -> C , C -> A}. The candidate keys are {A,B} and {C,B}. This is in 3NF but not BCNF because of {C->A} . Now it seems like this can't even be split into a lossless BCNF, let ...
user avatar
-2 votes
1 answer
96 views

Normalize to 1NF and 2NF, also 3NF (if possible)

I have a sample table with the dataset unnormalized, So I need to make it normalize in 1NF and 2NF and answer 5 different questions from those tables. Part of my dataset: (unnormalized) category |item ...
user avatar
0 votes
2 answers
38 views

Separate table to store long string identifiers

In my postgres DB, have an assets and a sales table. The current owner of an asset is identified by an Ethereum address with 42 characters, e.g. 0x81238432CE9b51Fbc2b59AFD74Ba8D09863B8B00. A sale has ...
user avatar
  • 101
1 vote
1 answer
16 views

How to model data which sometimes (but not always) has a subsection?

I'm trying to help my wife create a schema for a project of hers, and I'm somewhat stumped by some of her data and how to model it. She has a bunch of "standards" that go in to a database. ...
user avatar
  • 13
1 vote
1 answer
64 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, ...
user avatar
  • 13
1 vote
1 answer
92 views

Create an index for fields within an array of composite type

My question is a follow-up to the question answered here: https://stackoverflow.com/a/15041094/994263 Considering a table with an array column of a composite type, is it possible with PostgreSQL to ...
user avatar
  • 113
-1 votes
1 answer
16 views

Table normalization of given table

I've got a table and I'm supposed to figure out why it breaks with 1NF, 2NF and/or 3NF. I know the differences between these three, but I'm bad at telling what attributes are primary keys and what is ...
user avatar
1 vote
1 answer
35 views

How to enforce UNIQUE constraint for sixth normal form in PostgreSQL

I have a table person which I am planing to normalize to the sixth normal form. The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
user avatar
1 vote
0 answers
29 views

How to enforce NOT NULL for sixth normal form in PostgreSQL

I have a table person which I am planing to normalize to the sixth normal form. The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
user avatar
0 votes
1 answer
60 views

Does combining multiple tables into one table with more than 100 columns improve performance and overall design?

I have 10 tables without any type of foreign key relations between them. All the tables have the same primary key. In this case, "stock_id" is the primary key. All table columns describe a ...
user avatar
-1 votes
1 answer
56 views

How to query likes and user details for posts?

I'm pretty new to the world of databases and SQL. I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
user avatar
  • 101
0 votes
1 answer
156 views

Strong vs weak entity types on SQL

I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. ...
user avatar
1 vote
1 answer
96 views

normalisation in DBMS

For the relation schema R(A, B, C, D, E) with the following FDs FD1: {A,B,C} → {D,E} FD2: {B,C,D} → {A,E} FD3: {C} → {D} I Decomposed R into a set of BCNF relations. I got candidate keys as {A,B,C},...
user avatar
  • 21
-1 votes
1 answer
70 views

Normalisation from 2nf to bcnf

This is a question from a textbook Consider the Relation R={A, B, C, D, E, F, G, H } and the set of functional dependencies. A→C B→CG AD→EH C→DF A→H What is the key for R? Decompose R into 2NF, 3NF ...
user avatar
  • 3
0 votes
1 answer
48 views

How to create a trigger that generate a random specific output to insert into a table in MariaDB?

I have an account table which is laid out in 3NF: CREATE TABLE account ( account_id INT AUTO_INCREMENT, customer_id INT NOT NULL, account_type_id VARCHAR(15) NOT NULL, is_active ...
user avatar
  • 13
-1 votes
1 answer
68 views

How can I prove / disprove If A ↠ B then AC ↠ B

If A ↠ B then AC ↠ B is it true or false and if so can have example why?
user avatar
  • 1
0 votes
1 answer
44 views

Finding a balance with normalization and when to keep similar data together

I'm integrating with services that use OAuth, and the data I need to save to the database is very similar. However, there are slight differences with the first two implementations. They both have a ...
user avatar
  • 439
0 votes
1 answer
491 views

Unnest array values from multiple columns with dynamic unpivot

I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns. Example input: CREATE TABLE tbl_wide ( p_key integer PRIMARY KEY,...
user avatar
2 votes
1 answer
50 views

Is there an agreed upon definition of 1NF and 2NF? [closed]

I am trying to understand 1st and 2nd normal forms and I see lots of seemingly conflicting information, or just different sets of requirements which leaves me confused. I have a table that I want to ...
user avatar
0 votes
0 answers
121 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 ...
user avatar
  • 1
8 votes
2 answers
829 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 ...
user avatar
1 vote
1 answer
130 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 ...
user avatar
0 votes
0 answers
101 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 ...
user avatar
  • 121
1 vote
0 answers
279 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 ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
15 votes
3 answers
3k 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 ...
user avatar
  • 391
0 votes
1 answer
66 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: ...
user avatar
  • 9
0 votes
2 answers
97 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 ...
user avatar
  • 1,493
-1 votes
1 answer
376 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 ...
user avatar
  • 27
0 votes
1 answer
31 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 ...
user avatar
  • 153
0 votes
1 answer
428 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'...
user avatar
  • 13
1 vote
3 answers
255 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 -&...
user avatar
0 votes
2 answers
912 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 ...
user avatar
  • 13
1 vote
3 answers
119 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 ...
user avatar
-1 votes
2 answers
586 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 ...
user avatar
  • 7
0 votes
2 answers
48 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 ...
user avatar
0 votes
1 answer
30 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, ...
user avatar
  • 163
0 votes
1 answer
207 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 ...
user avatar
0 votes
2 answers
314 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 ...
user avatar
  • 11
-1 votes
1 answer
35 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>
user avatar
0 votes
3 answers
387 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 ...
user avatar
-1 votes
2 answers
39 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 ...
user avatar
  • 103
-1 votes
1 answer
565 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 ...
user avatar
0 votes
1 answer
99 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:
user avatar
0 votes
1 answer
101 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 ...
user avatar
  • 133
0 votes
0 answers
343 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 ...
user avatar
-1 votes
1 answer
134 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 ...
user avatar
  • 1
0 votes
2 answers
131 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&...
user avatar

1
2 3 4 5
13