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.

learn more… | top users | synonyms

21
votes
3answers
2k 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 ...
21
votes
8answers
2k 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 ...
19
votes
5answers
3k 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 ...
15
votes
6answers
3k 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 ...
15
votes
6answers
336 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 ...
14
votes
3answers
8k 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 ...
12
votes
4answers
13k 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, ...
12
votes
2answers
7k 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 ?
10
votes
6answers
1k 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. ...
8
votes
2answers
387 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 ...
8
votes
1answer
6k 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 ...
7
votes
2answers
4k 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 ...
7
votes
3answers
659 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 ...
7
votes
4answers
2k 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 -> ...
7
votes
1answer
761 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 ...
7
votes
2answers
154 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. ...
7
votes
3answers
3k 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 ...
7
votes
3answers
6k 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: ...
7
votes
2answers
682 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 ...
6
votes
3answers
290 views

Do higher normalized forms always meet the criteria of lower ones?

For example, a database that is 3NF is always 1NF and 2NF; does this hold for 4, 5, and 6?
6
votes
4answers
838 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 ...
6
votes
1answer
5k 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 ...
6
votes
1answer
210 views

IIS access log to SQL normalization

I am looking for insert IIS 6.0 access log ( 5 servers, and over 400MB daily ) to SQL database. What scares me is the size. There is a lot of information you are duplicating (i.e. site name, url, ...
5
votes
5answers
2k 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 ...
5
votes
2answers
497 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 ...
5
votes
3answers
396 views

Database Design Issues

My question consists of three parts: When can I be sure that my database design is perfect? Is returning to the database design to change some issues (i.e, adding new column, deleting a column, ...
5
votes
4answers
399 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 ...
5
votes
4answers
5k 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 ...
5
votes
2answers
423 views

Creating a table with many optional fields

I am a developer (student) looking for some advice on what best approach should I follow while creating a table to model an entity that has a number of optional fields. There exists a need to model ...
5
votes
3answers
935 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 ...
5
votes
3answers
4k views

When to apply normalization in database design

Hello database administrators. Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior ...
5
votes
2answers
877 views

Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
5
votes
2answers
667 views

2NF and 3NF How to do it

Given E(ABCDE) ABC are candidate keys Normalize into 2NF and 3NF As far as 2NF is concerned the solution is quite easy: E1(BD), E2(DE), E3(ABC) But with regard to 3NF I think I'm wrong if I say ...
5
votes
3answers
4k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to ...
5
votes
1answer
873 views

MySQL database normalization 3NF

I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After ...
5
votes
1answer
178 views

Decomposing a relation into 3NF/BCNF

I am having trouble decomposing a relation to 3NF/BCNF. I have determined that for the relation: R (A,B,C,D,E,F,G,H,I) where: {A -> B,C} {E -> F} {D -> I} {A,D -> G} {G -> H} ...
4
votes
5answers
14k 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.
4
votes
4answers
176 views

Should I normalize my string arrays, or just keep it a simple string?

I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain ...
4
votes
1answer
222 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 unnecessarily ...
4
votes
2answers
668 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 ...
4
votes
2answers
1k views

Constraint many to many table between two child tables

New to this exchange site so I hope my question isn't inappropriate. Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently. One table has a ...
4
votes
1answer
68 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
4
votes
1answer
320 views

Questions regarding second normal form

I understand that in order to fulfill 2.NF, attributes must not be dependent on part of the key. Now, the question is, let's say we have a relation R with a set of attributes {A,B,C,D,E,F,G,H,I,J,K} ...
4
votes
1answer
2k views

Database Design for Questions and Answers

I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to ...
4
votes
2answers
54 views

Anomalous Updates in Normalized Database

I've been trying to understand the three first normal forms of databases and seem to be missing something: Imagine a database with one table consisting of dances. There are 5 attributes: A Dance#, ...
4
votes
2answers
1k 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 ...
4
votes
2answers
1k views

Database Schema for an address book with different mailing lists

I'm helping a friend out with taking an old excel, single-page database which contains around 12 different columns that store information about: person first+last name email(s) current address ...
4
votes
2answers
233 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, ...
4
votes
3answers
120 views

Normalization/normal forms - May a field describe an other field?

Like this: CREATE TABLE persons( id serial8 NOT NULL PRIMARY KEY, name varchar, -- A lot of other fields date_of_birth timestamp with time zone, date_of_birth_precision ...
4
votes
1answer
359 views

I need help to migrate denormalized table content to normalized form- MS Access

I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships However, for ease of collecting ...