Skip to main content

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
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
4 votes
1 answer
3k views

Storing created/retired dates for a large amount of different entities

In the db I'm working on now almost every entity has these 4 columns: CreatedDate CreatedBy RetiredDate RetiredBy Usually this is used for logging purposes, and for some of the entities the ...
LainIwakura'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
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,849
3 votes
2 answers
4k views

Better defining a parent-child relationship on a table

Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses. We first ...
Human_AfterAll'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
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,774
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
1 vote
1 answer
328 views

How can I improve this table, and subsequently the performance of the queries I execute on it?

I have inherited this table of POS transaction level data. In my opinion it is terrible. There are 3.5 million rows, which span ~4 years, with a distribution of anywhere between 1 result and 100k ...
Adam Copley'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
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
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
5 votes
2 answers
4k 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 One-To-...
Ingó Vals's user avatar
4 votes
2 answers
3k views

Database normalization: Describing trait - foreign key to table, or varchar field with value?

My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order". In my ...
Sebastian's user avatar
  • 143
4 votes
4 answers
2k 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 ...
cornergraf's user avatar
3 votes
1 answer
9k views

How can I prove / disprove If A ↠ BC and A → B then A → C

/ From GATE 2007 IT: Consider the following implications relating to functional and multi valued dependencies given below, which may or may not be correct. A) If A ↠ B and A ↠ C then A → BC B) ...
abcd's user avatar
  • 33
2 votes
1 answer
4k views

How to remove data redundancy in product table if multiple suppliers are there for same product

I am designing a database where I have different products and suppliers. For example, I have a product, "mobile phone", say an Apple 5S. This will be sold through my website from different ...
Vijay Damale's user avatar
2 votes
1 answer
704 views

Multiple values in lookup fields

I know newer versions of MS Access allow multiple selections in lookup fields. For example, if I'm collecting information on my students, I can have a field called Race with lookup values that ...
maneesha's user avatar
  • 197
1 vote
1 answer
167 views

Given the functional dependencies of a relation is there more then one way to normalize it?

I"m studying normalization and have been reading through descriptions and examples online. One Concept that isn't clear to me is if the process can be automated at any point. I understand that a ...
Drew Verlee's user avatar
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
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
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
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
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
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

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
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
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
5 votes
3 answers
4k views

To Normalize or Not to Normalize

In general, I understand that normalization is usually beneficial even with the join costs. However, I came up with an interesting dilemma recently. What if the data is duplicated but unlikely to ...
Adam Thompson's user avatar
5 votes
2 answers
840 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 ...
qxc's user avatar
  • 53
5 votes
2 answers
1k 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 ...
Ryan David Ward's user avatar
4 votes
2 answers
857 views

3rd Normal Form violation?

I've got a photo review type of application, where the customer wants to have an option for the user to flag issues with the photos. So, for instance, a photo could be tagged as "fuzzy," "grainy," or "...
user1873604's user avatar
4 votes
2 answers
283 views

Is combining independent tables called normalisation?

So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc. These are all independent data with no foreign ...
yihtserns's user avatar
  • 143
3 votes
2 answers
2k views

What and How is the ID antipattern?

What is the ID antipattern and why / under which circumstances is it considered an antipattern? The id antipattern is having a unique ID column in each table. The ID is generated for each new ...
Martin's user avatar
  • 2,420
3 votes
2 answers
370 views

Simple table normalisation

In raw form, I have data with 2 columns: skills in welsh and stats for that skill. For storing these information in a database, would it be best to combine the stats with the categories, or split ...
BlackBox's user avatar
  • 133
2 votes
2 answers
1k views

Normalising shared entities and applying constraints

I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many. For example, one may have the generic entity "...
Louis Somers's user avatar
2 votes
2 answers
4k views

How to break table into two without losing performance?

According to https://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
Gili's user avatar
  • 1,029
2 votes
3 answers
3k views

Simple scenario needs to go from Unnormalized Data to 3NF - Review Please

NOTE: The Question has been updated. View Edit Revisions to view original question. The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so: [...
J86's user avatar
  • 331
2 votes
1 answer
5k views

3NF Decomposition - I'm not sure about this example

I am trying to make sense of an example of 3NF decomposition using the 4-step algorithm mentioned by Ullman here, but I'm not understanding what my lecturer is doing with the last step (or, worse, I'm ...
Tobia Tesan's user avatar
2 votes
2 answers
2k views

Dependency preserving decomposition

Let be the following relational scheme: F={AB → C; AC → B; AD → E; B → D; BC → A;E → G} Is the following decomposition without loss of dependency? R¹=(A,B),R²=(B,C),R³=(A,B,D,E),R⁴=(E,G) ...
Revolucion for Monica's user avatar
2 votes
2 answers
620 views

Is this table really in first normal form (1NF)?

Does this table comply with 1NF, even if CourseNo and StudentNo? Student(StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
Erik Eriksson's user avatar
2 votes
1 answer
44 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
user avatar
2 votes
1 answer
219 views

Is sql_variant compatible with First Normal Form (1NF)?

1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?
igelr's user avatar
  • 2,132
1 vote
1 answer
1k 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 ...
merit's user avatar
  • 47
1 vote
1 answer
1k views

Confusion in BCNF

I understood all the three normal forms which comes before BCNF but when it comes to the BCNF i am finding it extremely confusing and difficult and over the net also i found no good tutorial on it. ...
Ankur Trapasiya's user avatar
1 vote
1 answer
352 views

Need Help With BCNF Decomposition Problem

I'll try and keep it as quick and concise as I can: I'm currently a novice developer who's super eager to learn Database Normalization and Optimization so I can up my skill in Full-Stack development, ...
MacDadddy's user avatar
1 vote
2 answers
941 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: |-----------------------------------------------------------...
mydreamadsl's user avatar
1 vote
1 answer
176 views

Multiple one to many relationships in series

I am making a DB for storing reports that have following fields: Ad_system, Ad_Campaing, Ad_Group, Keyword, Date, Impressions, Clicks, Conversions Logic is that, Ad_system can have multiple ...
user111763's user avatar
1 vote
1 answer
83 views

Design a db table where rows may have NULL in multiple columns

I am trying to design a database for tour packages. Tour packages contains the detailed itinerary. Tour package duration may be between 3 and 30 days. I need to add the description of each day. If I ...
Saket Anand's user avatar