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.
13
votes
5answers
1k 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 ...
13
votes
3answers
602 views
Duplicate column for faster queries?
The title doesn't make to much sense but i couldn't think a better title for this problem.
I have the following tabels
Projects
id
name
Customers
id
id_project
name
Payments
id
id_customer
...
12
votes
6answers
249 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 ...
9
votes
5answers
1k views
Is database normalization dead?
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 ...
9
votes
2answers
2k 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 ?
7
votes
1answer
451 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
1answer
742 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
150 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
2answers
963 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 ...
6
votes
4answers
1k 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, ...
6
votes
2answers
160 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 ...
5
votes
3answers
189 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?
5
votes
2answers
334 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
1answer
184 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, ...
4
votes
3answers
321 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, ...
4
votes
3answers
520 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 ...
4
votes
4answers
1k 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 ...
4
votes
3answers
1k 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 ...
4
votes
1answer
813 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 ...
4
votes
2answers
345 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
372 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 ...
4
votes
3answers
1k 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 ...
4
votes
1answer
174 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 ...
4
votes
2answers
531 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:
...
3
votes
3answers
609 views
3NF Vs 2NF, which is more strict?
Which of these is true:
If a table is in 2nd normal-form then it must be in 3rd normal-form.
If a table is in 3rd normal-form then it must be in 2nd normal-form.
I know only one of them is ...
3
votes
2answers
121 views
Which tables to create according to relations
We are planning a new system with a new DB (MS SQL Server).
We have the following entities and the following relations:
Entities:
Clubs,
Chains,
PaymentTypes,
etc...
Relations:
Clubs-Chains (M-M);
...
3
votes
3answers
2k views
SQL table design for primary keys (best practice)
I have a User table that contains the following fields
ID (Auto-generated GUID)
UserName
Password
FirstName
LastName
Email
AddressID (GUID)
This is linked to the table Address (1-1 relation) which ...
3
votes
1answer
249 views
Normalized Table Structure for Logging
How can I normalize a table that has four columns for logging the latest activity for a single record:
Created On
Created By
Modified On
Modified By
We have dozens of tables, all with these 4 ...
3
votes
3answers
112 views
Optimisation Newbie: How much of a 'sin' is redundancy?
I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.
I have 2 SQL tables, as such:
VOTES
...
3
votes
2answers
159 views
Migrating and normalizing data
We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of ...
3
votes
2answers
404 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 ...
3
votes
1answer
335 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 ...
3
votes
1answer
67 views
Reporting Table Design
I'm writing a system that allows users to store values against each week in October and November and each day in December - starting from the first Monday. I'm holding the raw data entered by the ...
3
votes
2answers
375 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 ...
3
votes
1answer
101 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 ...
3
votes
2answers
60 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 ...
2
votes
4answers
2k 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.
2
votes
3answers
367 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 ...
2
votes
2answers
81 views
How to normalize data based only on the repetitions
Given a non-normalized data table, is there a good a strategy to attempt to normalize based on repeated data? Assume we have no information about the significance of the columns.
In a small example
...
2
votes
3answers
193 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 ...
2
votes
4answers
215 views
Does my table violate normalization rules?
I am developing an emulator for a game. My item table looks like this:
-- ----------------------------
-- Table structure for `items`
-- ----------------------------
DROP TABLE IF EXISTS `items`;
...
2
votes
1answer
802 views
Decomposition of a relation to 2NF then to 3NF
I'm currently studying for my exams and the trouble I am having is how to decompose a relation R with given functional dependencies into 2NF then 3NF.
For example for the following R and functional ...
2
votes
2answers
178 views
Writing to a DKNF database, but reading from a de-normalized table for performance?
Would the following approach be reasonable?
The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.
The Context: A database for ...
2
votes
2answers
307 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 ...
2
votes
3answers
257 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:
...
2
votes
2answers
123 views
Why is this relation in 3NF?
I have a relation:
R4 = {{T,U,V}, {T → U, U → T, T → V}}
I know from looking at the answer key that this relation is in BCNF.
I'm going through the process of rigorously determining what normal ...
2
votes
1answer
176 views
Splitting a column to normalized table
I have a de-normalized table as
Original De-Normalized Table
id text
23 first,second|third,fourth,fifth|sixth
I want to normalize the database to create the following tables
Table 1: (Split by ...
2
votes
2answers
118 views
design of maintenance database
Lets say i am designing a database that tracks maintenance of equipment.
Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc.
Some fields change ...
2
votes
1answer
499 views
Why is a Star Schema more normalized than a 3NF Schema?
According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas".
I'm confused, I thought 3NF is the most ...
2
votes
3answers
173 views
Does this database table violate normalization rules?
I’m working with Access 2010.
I’m developing a database for a public art program – we paint large scale murals on walls.
The database tracks blank walls in the city as potential sites for murals. It ...
