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
0 votes
0 answers
35 views

Why is the relation in 3NF?

A relation is in 3NF (Third Normal Form) if it is in 2NF (Second Normal Form) and all the FDs (functional dependencies) in the relation are in the form X -> Y, where X is a superkey (a set of ...
1 vote
2 answers
89 views

Help - Database design

We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex. Requirement - We have two webforms; ...
0 votes
0 answers
48 views

Foreign Keys As Composite Primary key

Can two non-key attributes of the same table be the composite primary key of another table?? In relation R(BCFGH) following set of FDs hold: B -> CFH CH -> G Since CH -> G is transitive ...
2 votes
0 answers
43 views

For selling items in a game, should the shop and item tables be merged?

I'm developing a game that uses a Postgres database. In the game players can have many different items, and each player can have multiples of an item (example: 200 apples). My item table currently ...
3 votes
2 answers
51 views

Any benefit in replacing a varchar for an index in the main table?

I'm 'inherithing' the job of a database admin for a very small team (3-4 people) and while I know how to execute SQL queries relatively well and understand the basics of programming efficiency , I ...
  • 133
0 votes
3 answers
61 views

How do I handle an INSERT to 2 tables within a transaction?

I have 3 tables in a Postgres DB: UserType -> ID, creation, delete, update, name User -> ID, creation, deleted, update, user_type_id Employee -> ID, creation, deleted, update, user_id (fk) ...
  • 1
3 votes
1 answer
29 views

Do "range" data types in PostgreSQL correspond to First Normal Form?

I have a task for my university project to confirm that the database I made is in Third Normal Form. I decided to use the "daterange" data type for one of the columns in my database, which ...
  • 33
0 votes
1 answer
57 views

Is a "static" FK bad design? [duplicate]

I have a table table1 where records can have different status. If status=10 I need to store more information. In order to keep the db normalised I'm storing the extra information in a separate table, ...
  • 107
0 votes
1 answer
83 views

Many to many relationship when join table takes two same entities and an additional one

as of right now I'm trying to design a database which contains data involving football matches. I have two tables: Match & Football Team both containing various attributes. These are connected via ...
  • 63
2 votes
0 answers
41 views

One-to-One? or One-to-Zero-or-One?

Scenario: I am being asked to create an HR training tracker for employees of a business. in this database I am using two tables: EMPLOYEE(EMPLID, LastName, FirstName, PrimaryEmail) with EMPLID as the ...
3 votes
2 answers
260 views

can two tables have same primary key and at the same time this primary key is a foreign key for one of them?

I have a table user id (PK) email username password status user_role profile_image created_at and another table: healthdata id (PK) age gender height goal weight ...
  • 39
-4 votes
1 answer
34 views

Is it normal to have 300 queries to display a single page? [closed]

The total number of queries is: Q1 * Q2 * Q3, it might be 25 * 3 * 4 = 300, or maybe even more. all of these is for a single user displaying a single page! Should I duplicate the data and store them ...
-1 votes
1 answer
32 views

Help with tables normalization for delivery system

i have a task to design a SQL database to represent a "delivery". I've been able to solve most of the needs, however i have doubts with one relationship: A delivery has an origin and a ...
  • 1
-1 votes
1 answer
45 views

Why aren't normal forms defined more simply?

I believe that 2nd and 3rd normal form definitions could obviously be specified with less complexity, but they're not. The definition found in textbooks is something like this: 2NF: No partial FD ...
  • 9
0 votes
1 answer
228 views

Set into multiple rows [duplicate]

I have the following data SQL> SELECT id, relatedIdInASet from tab; +----+------------------+ | id | relatedIdInASet | +----+------------------+ | 1 | [12,34,56] | | 2 | [11,12,22,34] |...
  • 449
1 vote
1 answer
39 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. ...
0 votes
0 answers
17 views

Correctly handling multi-preferences updates in SQL

I have a Postgres schema as (simplified): A user can have n number of car preferences. users_table ------------ id auto_increment int (PK) name text cars_table ------------- id auto_increment int (PK)...
0 votes
0 answers
60 views

Design proper schema for flexible entity field

I have some entities modeled like on the picture. Every entity have field classifications which contains list of Classification. Classification itself can have any name and value, all represented as ...
  • 101
0 votes
1 answer
105 views

Normalizing an old database

I have a need to normalize an old PostgreSQL database, and I have an idea on how to do it which I would like to hear your opinions about. The aspect that needs to be fixed is this: We have a consumer ...
  • 101
-1 votes
2 answers
757 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 ...
0 votes
1 answer
355 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 ...
-2 votes
1 answer
140 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 ...
1 vote
2 answers
115 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 ...
  • 111
1 vote
1 answer
21 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. ...
  • 13
1 vote
1 answer
228 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, ...
  • 23
1 vote
2 answers
1k 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 ...
  • 113
-1 votes
1 answer
20 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 ...
2 votes
1 answer
83 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 ...
2 votes
0 answers
46 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 ...
0 votes
1 answer
121 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 ...
-1 votes
1 answer
398 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 ...
  • 101
0 votes
1 answer
840 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. ...
1 vote
1 answer
105 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},...
  • 21
-1 votes
1 answer
174 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 ...
  • 3
0 votes
1 answer
252 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 ...
  • 13
-1 votes
1 answer
170 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?
  • 1
0 votes
1 answer
47 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 ...
  • 437
0 votes
1 answer
2k 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,...
2 votes
1 answer
81 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 ...
0 votes
0 answers
245 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 ...
  • 1
8 votes
2 answers
1k 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
1 answer
278 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
0 answers
182 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 ...
  • 121
1 vote
0 answers
403 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
1 answer
61 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 ...
16 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 ...
  • 423
0 votes
1 answer
101 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: ...
  • 9
0 votes
2 answers
271 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,553
0 votes
1 answer
824 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 ...
  • 37
0 votes
1 answer
40 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 ...
  • 235

1
2 3 4 5
14