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

3
votes
2answers
147 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
3k 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
2answers
72 views

Database normalization: Which is the best fit?

I have 3 different types of data set: Notification: start_date end_date notification_type country_id state_id Campaign: start_date end_date user_notification Transaction email ...
3
votes
2answers
180 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 ...
3
votes
2answers
136 views

Is this in 1NF?

I'm an A-level Computer Science student, and my teacher can't explain how to get me from UNF to 1NF. I've researched a little, but I'm not sure as to whether this table (below) is in 1NF or not. I've ...
3
votes
1answer
248 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 ...
3
votes
1answer
7k 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 ...
3
votes
1answer
1k views

Normalized Table Structure for Logging

How can I normalize a table that has four columns for logging the latest activity for a single row: Created On Created By Modified On Modified By We have dozens of tables, all with these 4 ...
3
votes
3answers
147 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
84 views

Survey Database - Respondent Table (1NF or 3NF - Pros and Cons)

Building out a Survey ERD ODS layer. Now i'm debating on the design for my Respondent Table. Not sure if its best to have it as 1NF or 3NF. FOr a quick back story I get a survey extract with ...
3
votes
2answers
1k 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 ...
3
votes
2answers
341 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
4answers
368 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 ...
3
votes
4answers
4k 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 ...
3
votes
2answers
211 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 ...
3
votes
1answer
60 views

Does the definition of 2NF prohibit _transitive_ dependencies on a partial key or only direct ones?

We know a relation is in 2NF iff it is in 1NF and no non-prime attribute is dependent on a proper subset of the candidate key. But is "dependent" in this definition strict or loose in the following ...
3
votes
2answers
112 views

Is two unrelated tables with foreign keys to a third table breaking first normal form?

I was wondering if this breaks first normal form. AddressTable Id - PK Street City State BusinessTable Id - PK BusinessName AddressId - FK PersonTable Id ...
3
votes
1answer
239 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
69 views

Should I normalise this database?

I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system. I have two table designs in mind. One is this: CREATE TABLE ...
3
votes
1answer
59 views

Entity Relationship Design; The best scenario

TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume? I have experience working with large government database systems ...
3
votes
1answer
97 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) ...
3
votes
1answer
158 views

If all variables are a subset of the candidate key, is the database design 5NF?

I have a table called LogMessages, which has the following columns: Level A numeric value which represents Trace, Debug, Info, Warning, Error or Fatal Time A UTC time Message Foreign key to a ...
3
votes
2answers
2k views

MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

Here is my database design. I've been doing a lot of reading and still don't really know what I'm doing. Hoping you guys had tips! My DB will have millions of entries. Many "People" rows will have ...
3
votes
1answer
740 views

What is best practice to organize / normalize database tables?

I manage group that uses a database that is about 12 years old. There are more than 200 tables / views that have been created by various people over the years, many of them no longer with us. Most ...
3
votes
1answer
54 views

Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

My problem is best explained by example: Suppose I have a database about chess games and players. Which one of the following structures should I choose Option 1) player (player_id,name... etc) ...
3
votes
2answers
64 views

Best design for a many:many relationship issue

We have three types of entity, each with their own table: table:A(a_id), table:B(b_id), table:C(c_id) Each of these entities can have any number of email addresses and linked Twitter accounts. ...
3
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 ...
3
votes
1answer
108 views

Modeling discounts - Product specific and Customer specific discounts

I have a reasonably simple database but have the following problem: I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to ...
3
votes
0answers
75 views

Superkeys of this relation

I am trying to find the superkeys of this relation, but I am having troubles finding out how many superkeys there are and exactly what they are. I figured out that the candidate keys were ...
2
votes
6answers
947 views

Is normalization process needed?

Well after learning DBMS as a subject i got so many questions in mind. Normalization is one of them. As i learnt it there was a lot more confusion and i found that whatever we do in normalization ...
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 ...
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
2answers
147 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
4answers
101 views

Row for each date, or 31 columns?

Currently I have a system whose front-end looks like this: The following query: SELECT `l`.*, `s`.`month`, `s`.`year` FROM `summarylines` `l` JOIN `summaries` `s` ON ...
2
votes
4answers
367 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
126 views

What would be the Candidate key (s) of this relation?

R(ABCDEPG) F={AB->CD, DE->P, C->E, P->C, B->G} To my consideration, the candidate keys are: AB, AG, CD, DE. Am I correct? Why or why not?
2
votes
1answer
268 views

How to model schedule and keep good constraints?

I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will ...
2
votes
3answers
230 views

is id and phone number enough to uniquely identify a row in phone numbers

Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the ...
2
votes
2answers
495 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 ...
2
votes
2answers
301 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
57 views

What are the prime and non-prime attributes in this relation?

R(HIJKLMNO) L -> MNO HI -> JKLMNO J -> KL K -> H HI is a key. So, H is a prime attribute. But, H is also in non-key part. What are the prime and non-prime attributes in this ...
2
votes
1answer
53 views

Does schema normalization enhances query efficiency

I understand the importance of normalization in reducing data redundancy and various anomalies but I am wondering if it can also enhance the performance of your query?
2
votes
2answers
323 views

Can somebody help me normalise this database/check my workings

I am new to database design/normalisation and functional dependencies. I have started a new database class in school this week and we have been given a worksheet that requires the following: This is ...
2
votes
2answers
189 views

Normalizing nearly identical tables

Background I'm managing a relatively small database project in which we are adding support for reporting involving status updates for items in out product table. I was bit thrown into this and I've ...
2
votes
3answers
409 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: ...
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: ...
2
votes
2answers
232 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
2answers
59 views

Table “normalization” and tradeoffs

It's been a while since my databases course in university, so bear with me as I try to be unambiguous here. In the domain I'm working in, there are Sources and Destinations. Both have 2D coordinates ...
2
votes
1answer
213 views

Data normalization (from 0NF to 1NF)

I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this "A relation ...