Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
37 votes
3 answers
10k views

Duplicate column for faster queries?

The title doesn't make too much sense, but I couldn't think a better title for this problem. I have the following tables Projects id name Customers id id_project name Payments id id_customer ...
Gabriel Solomon's user avatar
35 votes
5 answers
9k views

How far should you go with normalization?

I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there ...
Fergus's user avatar
  • 453
27 votes
3 answers
14k views

How to have a one-to-many relationship with a privileged child?

I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
cubetwo1729's user avatar
26 votes
7 answers
7k views

What problems are solved by splitting street addresses into individual columns?

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
Greg Burghardt's user avatar
26 votes
3 answers
44k views

Is there a tool to check if my database is normalized to the third normal form?

I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, ...
ack__'s user avatar
  • 363
20 votes
4 answers
38k views

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small ...
Schmitty23's user avatar
19 votes
1 answer
32k views

Designing a friendships database structure: Should I use a multivalued column?

Say I have a table called User_FriendList, which has the following characteristics: CREATE TABLE User_FriendList ( ID ..., User_ID..., FriendList_IDs..., CONSTRAINT User_Friendlist_PK ...
yeln's user avatar
  • 293
16 votes
6 answers
6k views

Is database normalization dead? [closed]

I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing ...
PhD's user avatar
  • 263
16 votes
3 answers
4k 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 ...
paul23's user avatar
  • 473
16 votes
6 answers
402 views

Normalization: Is it considered compliant to split static, numeric values like a year into their own table?

I am having an interesting discussion with another database designer about normalization. In this example, we have a GameTitles table and each record must contain the year in which the game was ...
stoogemuffin's user avatar
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 ...
dpolaristar's user avatar
15 votes
4 answers
6k views

Blockchain (Bitcoin) as a database?

I was reading this BBC News article and the following excerpt, caught my attention. It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically ...
James Jenkins's user avatar
13 votes
2 answers
12k views

Likes or votes for posts

I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would ...
Harshit's user avatar
  • 247
13 votes
2 answers
18k views

Normalization Exercise Resources

I want to sharpen my database normalization skills. Where can I find good quality beginner-to-advanced level exercises (with solutions) on the web ?
mumtaz's user avatar
  • 767
13 votes
1 answer
15k views

Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies. Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
I'll-Be-Back's user avatar
13 votes
3 answers
15k views

Best practices for history/temporal tables?

Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright: ...
cubetwo1729's user avatar
12 votes
6 answers
9k views

Explaining 2NF vs 3NF with an example

I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students. ...
finsalscollons's user avatar
11 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 ...
ReynierPM's user avatar
  • 1,764
11 votes
2 answers
11k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
Googlebot's user avatar
  • 4,531
11 votes
5 answers
2k views

First Normal Form: Definitive Definition

I am trying to get a definitive version of what is First Normal Form. Everything I read has a slightly different spin. Many authorities, such as Date, say that by definition a relation is always in ...
Manngo's user avatar
  • 2,839
11 votes
2 answers
8k views

Enforce an "At least one" Relationship

The Many-to-many (data model) Wikipedia article uses the example of Authors and Books: For example, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be ...
Steven's user avatar
  • 319
9 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 ...
Marnix.hoh's user avatar
9 votes
2 answers
6k views

Is a table without a primary key normalized?

In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key. ...
Alex's user avatar
  • 93
9 votes
4 answers
10k views

What exactly is overlapping candidate key?

Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests? Consider the below relation R(L,M,N,O,P) { M -> O NO -> ...
vikkyhacks's user avatar
9 votes
2 answers
1k views

How can I losslessly decompose this table?

I have a table with the following PK (primary key): child_id integer parent_id integer date datetime child_id and parent_id are FKs (foreign keys) to entity tables. The child table also ...
trevor's user avatar
  • 99
9 votes
2 answers
2k views

Best relational database structure for this data

I'm in the process of creating a database scheme for the following scenario: There are users Users have roles (such as "Developer" or "CEO") Roles have applications (such as "Topdesk") Applications ...
Luc's user avatar
  • 193
8 votes
5 answers
6k views

What normal form does a surrogate key violate?

I have following question: "What normal form does a surrogate key violate?" My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that ...
Marco's user avatar
  • 91
8 votes
6 answers
2k views

Does an excessive table violate normalization rules?

Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical. There are four tables: ** School ** School Id, School ...
Burgan's user avatar
  • 183
8 votes
2 answers
669 views

Am I violating any NF rule on my database design?

I am a newbie on creating database... I need to create it for my recruitment web application. My application needs to schedule screenings, exams and interviews of applicants and save result in the ...
newbie's user avatar
  • 181
8 votes
2 answers
15k views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
matteeyah's user avatar
  • 217
8 votes
4 answers
9k views

Separate archive tables or soft delete for inventory database

I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to ...
TheSecretSquad's user avatar
7 votes
4 answers
3k views

Is it possible to get rid of so-called 'Spurious Tuples' completely?

Is it possible to get rid of so-called 'Spurious Tuples' completely? For example: In this example from a textbook, there is an original table: I don't see anything wrong with the two projections of ...
user avatar
7 votes
1 answer
1k views

Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys?

I have come across two flavours of the normalization procedure while referring the internet and textbooks. viz. Type 1. Normal forms based only on the primary key. In this type, -> 2NF disallows ...
Nabeel's user avatar
  • 73
7 votes
1 answer
10k views

2NF decomposition + database normalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
cpowel2's user avatar
  • 183
7 votes
2 answers
2k views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
Harley's user avatar
  • 73
7 votes
4 answers
9k views

First Normal Form, why is it good and how does it reduce redundancy

I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database ...
Celeritas's user avatar
  • 893
7 votes
2 answers
2k views

Good reason to denormalize?

I have a database with about 40-50 tables. All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project"). Each table is joined to its ...
sqlbattsman's user avatar
7 votes
3 answers
8k views

When to apply normalization in database design

Prior to learning about normalization I would usually read the scenario/description in the requirements and draw up an ER diagram (using Chen's notation) using nouns as entities and verbs as clues to ...
morris_wiemp's user avatar
6 votes
8 answers
44k views

What is Atomic Relation in First Normal Form

I have read the definition of 1NF which is, "If each attribute of relation is atomic". Please tell me what is Atomic.
Siddiqui's user avatar
  • 171
6 votes
4 answers
2k views

How can a database be normalized when an optional field causes denormalization?

Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this: A ...
Shawn McKnight's user avatar
6 votes
2 answers
332 views

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

Say there are entities called singulars, and entities called relationships. It takes exactly two singulars to make up a relationships entity. That pair of singulars can't be repeated elsewhere in ...
groovenectar's user avatar
6 votes
3 answers
4k views

Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)

I’m working on a data modeling project and I’m trying to figure out the best data modeling approach for a history table, which has only four columns: CREATE TABLE FooHistory ( SecurityID INT (FK)...
Harvey A.'s user avatar
6 votes
1 answer
8k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
Jim Stewart's user avatar
6 votes
4 answers
18k views

Storing arrays of integers in database (for efficient select)

I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of items....
user avatar
6 votes
1 answer
178 views

Is it possible to combine 7 sources without a UNION?

I have a source table that looks essentially like this: EmployeeCode WeekStartDate HoursWorkedDay1 HoursWorkedDay2 HoursWorkedDay3 HoursWorkedDay4 HoursWorkedDay5 HoursWorkedDay6 HoursWorkedDay7 The ...
Mathieu Guindon's user avatar
6 votes
2 answers
24k views

How does normalization fix the three types of update anomalies?

I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). The three DB ...
the_butler's user avatar
6 votes
1 answer
875 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 "...
Ascendant's user avatar
  • 429
6 votes
2 answers
3k views

Separate or Combine 2 Tables with Similar Attributes?

I have two kind of users in my business system: Customer and Employee. Both user have Username, Password, Fullname, Phone Number, Email, and other similar attributes. I have a difficulty to ...
Willy Lazuardi's user avatar
6 votes
3 answers
2k views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
Jim's user avatar
  • 163
6 votes
2 answers
3k views

Using IDs from multiple tables in a single column

One of my co-workers created a schema similar to the following. This is a simplified schema including only the parts necessary to address this question. The system rules are as follows: ...
DCNYAM's user avatar
  • 193

1
2 3 4 5
14