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

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 ...
3
votes
1answer
291 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 ...
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 ...
1
vote
1answer
113 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 ...
3
votes
4answers
612 views

Normalization for postal addresses in US (address, county, city, state, zipcode)?

I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get ...
2
votes
2answers
1k 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 ...
5
votes
2answers
495 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 ...
2
votes
1answer
362 views

Normalisation of practise database [closed]

I am a newbie to database design, and I'm a bit confused about how to create a database with minimum redundancy. Suppose I have a table abc with repetition of data in it and no two rows are same. Now ...
2
votes
1answer
460 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 ...
1
vote
2answers
54 views

How to normalize a table that contains a CK not included in the PK?

I have the following table. The PK is model_ID. Assuming that the modelName field is unique(that is, no two brands can have an identical modelName), there is a transitive dependency between modelName ...
1
vote
1answer
127 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 ...
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 ...
3
votes
1answer
127 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) ...
2
votes
3answers
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: ...
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. ...
1
vote
1answer
160 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 suppliers ...
1
vote
1answer
1k 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 ...
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 ...
3
votes
2answers
189 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 ...
3
votes
3answers
2k 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
2answers
64 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? ...
2
votes
1answer
37 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, ...
2
votes
1answer
3k 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 ...
2
votes
3answers
452 views

Database design for conditional detail table

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables: ...
1
vote
1answer
61 views

Apply Definition of MVD (multivalued dependency) to Example?

Supposedly this multivalued dependency (MVD) and this functional dependency (FD): {W} ↠ {N,D} and {W} → {N} hold in this table: V W N D ================= | v | w | n | 1 | ...
1
vote
1answer
109 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
2answers
249 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 ...
1
vote
2answers
1k views

How to break table into two without losing performance?

According to http://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 ...
1
vote
3answers
2k views

how to convert a table into second normal form

I am really struggling to convert the table into second normal form ive tryed examples which are much easier however i can't seem to be able to identify the keys for this one and work my way forward. ...
1
vote
1answer
827 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. ...
0
votes
2answers
254 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 ...