Hot answers tagged normalization
25
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 ...
24
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 ...
12
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 ...
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 ...
12
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 ...
12
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 ...
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
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 ...
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
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 ...
8
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 ...
8
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
...
7
You should use a lookup table
Not all clients will use the ENUM in the application
You will get a reporting or MIS or Excel app connecting at some point
How can do "NOT EXIST" otherwise?
You will be asked this
You won't know about client enum changes
Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause
...
7
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 ...
7
It isn't necessary to have a surrogate primary key on your clubs_chains table.
The combination of the two foreign keys in clubs_chains is adequate for the primary key.
You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key. This might be helpful ...
6
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 ...
6
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 ...
6
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 ...
6
Generally, I don't add redundant columns unless I really need too.
Running a COUNT over a set of data is quite efficient in any RDBMS.
Consider this is a read over indexed (hopefully) cached data to get the count will beat the the 2nd write in to maintain the denormlaised column. This write requires more resources/locking/longer transaction etc which ...
6
I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:
CREATE TABLE dbo.Users
(
UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
Username NVARCHAR(128) NOT NULL UNIQUE,
/* , other columns */
);
CREATE TABLE dbo.Tables
(
...
6
Another way (without Nulls) 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 sane) if you don't have Id columns all over your tables ...
6
No it is not.
Consider this data
id | name | city | state | country
----------------------------------------------
1 | john | Los Angeles | California | USA
2 | mary | San Antonio | Texas | USA
3 | joe | Los Angeles | California | USA
It is clear that (city,state,country) is a candidate key for another table. Then, you would quickly ...
6
You could consider a cart to be a storage location.
Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so:
If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table.
This is not a ...
5
1) When to be sure that your database design is perfect?
Your design is never perfect because the business logic and amount of data is always changing. Perfect is difficult to define
I've seen systems that were great on deployment but had poor performance after a few years of data were added. The regrettable trend to treat a database like a black box by ...
5
It isn't. But because the database is usually foundational to the business (eg applications are built on top of it), it is worth working hard to get it in the best shape you can. "best" is a practical consideration and depends on your needs, not a measure of theoretical or ideological perfection.
Requirements change - if your database doesn't change in step ...
5
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 ...
5
Every rule, every process, every pattern that is taught in programming courses is an effort to try to "institutionalize" common sense. If all of your developers have perfect common sense at all times and are clear-headed and insightful, then you don't need to follow anybody's rules, processes or patterns.
However, as the saying goes: "Common sense ain't" - ...
5
The design choices you describe are not directly related to normalization.
I agree there should be a lookup table.
I think an OrderStatusID value would increase redundancy. The status (text) value presumably already satisfies many of the qualities of a good key: unique, stable, narrow, familiar to users, etc. Referential integrity can be applied to VARCHAR ...
5
Arguably a surrogate key is not the natural key of the table, so it could be said to violate the 'nothing but the key' principle of 3NF. In practice a surrogate key is just a place holder for the natural key, so this argument is academic at best.
Some obscure normal forms require composite keys to become relevant. 5NF comes to mind in this case as it ...
Only top voted, non community-wiki answers of a minimum length are eligible
