Hot answers tagged

36

It seems you are asking if denormalization makes sense. Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access ...


28

You should go as far as you should, and no further. Of course. ~ The problem may be that this is a bit of an art, and it's why this isn't a pure science. Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child ...


19

Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target. However... I would venture to say that there could not be such a tool. Normalization, technically, is an attribute of each table. Within a given database, different tables may have ...


17

One reason for normalisation is to remove data modification anomalies ORMs usually do not support this. I have many examples of Hibernate-designed databases that break this principle: bloated (string repeated over 100s of millions of rows) no lookup tables (see above) no DRI (constraints, keys) varchar clustered indexes unnecessary link tables (eg ...


15

The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form. Your databse with the concatenated user IDs (first version) is not in first normal form. See https://en.wikipedia.org/wiki/Database_normalization for why and how normalisation is generally considered good. In ...


15

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise. ...


14

1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF ⊂ 5NF ⊂ 6NF


14

The other database designer is simply wrong, but your reasoning is wrong as well. Assume you start with this table, which has a single candidate key, "game_title". Table: game_titles game_title year_first_released -- The first game 1998 The second game 1999 Best game: the third one 2001 The ...


14

If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms. To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as ...


14

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB. @Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you ...


13

it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Surrogate keys don't break 2NF. 2NF says "If a column is dependant on only part of a multi-valued key, remove that column to a separate table." They stipulate that it's just better to have everything as one big table no ...


12

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.) "Atomic" has never really ...


12

Following are the resources which I'm using to brush-up my database skills and to teach newbies... Animated DataBase Courseware From Database Design to Advanced Concepts wofford-ecs.org Database Design - Normalization db4u Normalization exercises http://www.sql-ex.ru/ Tons of sql exercises ...


12

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key. Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that ...


11

A database in 3NF is in 2NF also. A simple mnemonic is: The key The whole key and nothing but the key So help me Codd 2NF states that a table contains no fields that are logically a function of a part of the primary key. 3NF states that a table contains no fields that are logically a function of any field of the table but the 'whole' key. 3NF can be ...


11

The more things change, the more they stay the same. There have always been lazy developers who cut corners or just don't know or want to follow best practices. A lot of the time they can get away with it in smaller applications. It used to be jamming COBOL-inspired data structures into early RDBMS, or the God-awful mess that was dBase. Now it's ORMs and ...


11

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


11

I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all. Using the blockchain as a live database: Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes. Basically a excel sheet without any computation capabilities that just ...


10

There are few things to consider other than normalization. For instance, you have a column for AGE. Are you going to update that every year? How will you know when to do that? The same goes for years of experience. There are some columns that will probably have multiple values for each applicant: School, Course, etc. You may also want to check your ...


10

There's a little aphorism that goes: The key (1NF) The whole key (2NF) And nothing but the key (3NF) . . . So help me Codd In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3 etc.). R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH, D ...


10

Arguably, it doesn't. Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys That is, introducing surrogate keys at the start of the design process is wrong. Even though ...


9

I agree that memory constraints did bear a direct correlation to normalization... Memory constraints still matter. Quantity isn't a problem, speed is. CPUs aren't getting any faster at the moment (We're getting more core's, not cycles per second) Modern CPU architectures attempt to overcome the speed limitation by providing separate memory for each ...


9

This sounds like a really simply one-to-many relationship. For SQL Server, I would write this like: CREATE TABLE Devices ( DeviceID INT , DeviceName nvarchar(255) ); CREATE TABLE Cards ( CardID INT , CardName nvarchar(255) , DeviceID INT ); CREATE TABLE Ports ( PortID INT , PortName nvarchar(255) , CardID INT ); INSERT ...


8

Yes, that's the way it's defined.


8

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N). Additional explanation of the above, originally left in comments: All overlapping ...


8

This is a great question. Normalization beyond BCNF is extremely hard to understand. Hopefully I can provide an answer that makes sense. I struggled with these concepts for over 20 years before finally making sense of them thanks to Fabian Pascal's Practical Database Foundation Series. The example provided is an EmpRoleProj R-table that looks like so: ...


8

1. Provided that sku and item_number will always hold unique values I consider that you have come to the answer yourself by means of the identification of item_number as an optional attribute (or column at the implementation phase), i.e., determining that it does not apply to all the product occurrences (rows). Therefore, from a logical point of view, it ...


7

You would be better to rewrite the query as: SELECT payments.* FROM customers JOIN payments ON payments.id_customer = customers.id WHERE customers.id_project = 5 While this seems less concise and a good query planner will see what you are trying to do and run your correlated sub-query as the above join instead, a bad query planner may end up ...


7

Normalization is a goal only when it supports your data model well enough to warrant it. It is meant to be a guide to allow growth, management and maintainability. Remember that the book on normalization, nor its writer are going to build or maintain your database or its application. A good read on the subject of "too much normalization" is here. And, yes ...


7

To answer your question, yes normalization is needed. Common sense is a relative term and is open to interpretation. RDBMS's have been around since the 1970's. Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed. The most complex time-consuming problems I have worked ...



Only top voted, non community-wiki answers of a minimum length are eligible